🐘PostgreSQL

Queries, psql meta-commands, and maintenance.

Databases · 3 sections

psql meta-commands

List databases / tables

bash

\l
\dt

Describe table

bash

\d users

Timing + expanded output

bash

\timing on
\x

Upsert

sql

INSERT INTO tags (name) VALUES ('go')
ON CONFLICT (name) DO UPDATE SET updated_at = now();

Find duplicates

sql

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Paging with keysetStable under inserts vs OFFSET

sql

SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Table + index size

sql

SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

Long-running queries

sql

SELECT pid, now() - query_start AS runtime, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY runtime DESC;