A handful of PostgreSQL one-liners I keep going back to.
Filter rows from today onwards
1SELECT * FROM your_table
2WHERE any_timestamp >= CURRENT_DATE;CURRENT_DATE is promoted to a timestamp at midnight in the session timezone — match the timezone you actually care about.
Reset a sequence
1ALTER SEQUENCE users_id_seq RESTART WITH 5;After a bulk import or restore, resync the sequence to the table's current max instead:
1SELECT setval('users_id_seq', (SELECT COALESCE(MAX(id), 1) FROM users));Watch out for setval('seq', N) — that sets is_called=true, so the next value generated is N+1. Pass false as the third argument if you want N itself next.
Database size
1SELECT pg_size_pretty(pg_database_size('your_database'));Table size
1SELECT pg_size_pretty(pg_total_relation_size('your_table'));pg_total_relation_size covers the heap, indexes, and TOAST. pg_relation_size is heap-only — useful when you want to see how much is actual data vs index overhead.
Delete duplicate rows
The PostgreSQL idiom uses ctid (the physical row identifier), so it works whether or not the table has a primary key:
1DELETE FROM your_table a
2USING your_table b
3WHERE a.ctid > b.ctid
4 AND a.col1 = b.col1
5 AND a.col2 = b.col2;Keeps one row per (col1, col2) group and drops the rest.
Change a table's owner
1ALTER TABLE your_table OWNER TO new_owner;