PostgreSQL Cheatsheet
psql meta-commands + Postgres-specific SQL you'll reach for daily.
1 credit
psql meta-commands
9 itemsConnect
psql -h host -U user -d dbList DBs
\lList tables
\dtDescribe table
\d+ usersList roles
\duShow schemas
\dnTiming queries
\timingRead SQL file
\i schema.sqlQuit
\qTypes / features
5 itemsJSONB column
CREATE TABLE x (meta JSONB) / meta->>'key' / meta @> '{"k":1}'Array
tags TEXT[] / WHERE 'admin' = ANY(roles)UUID default
id UUID PRIMARY KEY DEFAULT gen_random_uuid()Enums
CREATE TYPE status AS ENUM ('new','done')Generated column
full_name TEXT GENERATED ALWAYS AS (first||' '||last) STOREDIndexing
5 itemsStandard B-tree
CREATE INDEX idx_users_email ON users(email);Partial
CREATE INDEX ... ON orders(user_id) WHERE status='open';Expression
CREATE INDEX ... ON users(lower(email));JSONB path (GIN)
CREATE INDEX ... ON docs USING gin(meta);Inspect usage
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0; -- unusedPerformance
5 itemsPlan + timings
EXPLAIN ANALYZE SELECT ...;Long-running queries
SELECT pid, now()-query_start AS age, query FROM pg_stat_activity WHERE state='active' ORDER BY age DESC;Table sizes
SELECT pg_size_pretty(pg_total_relation_size('orders'));Kill query
SELECT pg_cancel_backend(<pid>); -- gentler; pg_terminate_backend for forceVacuum + analyze
VACUUM ANALYZE orders;