Cheat sheet · Back end and data
PostgreSQL cheat sheet
psql commands, handy types, JSON, upserts, query plans and backups for PostgreSQL.
- Sections
- 6
- Items
- 27
- Area
- Back end and data
- Download
- PNG, PDF
Cheat sheet
PostgreSQL
psql commands, handy types, JSON, upserts, query plans and backups for PostgreSQL.
psql commands
psql -h host -U user -d dbname- Connect to a database
\l- List databases
\c dbname- Switch database
\dt- List tables
\d tablename- Show a table's columns and indexes
\du- List roles (users)
\x- Toggle a tall, readable output
\q- Leave psql
Handy features
id BIGINT GENERATED ALWAYS AS IDENTITY- An automatic number
created_at TIMESTAMPTZ DEFAULT now()- A time with time zone, set on insert
name ILIKE '%asha%'- Search ignoring case
coalesce(a, b)- The first value that is not null
generate_series(1, 10)- A list of numbers as rows
INSERT ... RETURNING id- Get values back from the insert
Upsert
INSERT INTO t (k, v) VALUES (1, 'a') ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;- Insert, or update if the key exists
ON CONFLICT DO NOTHING- Skip a row that already exists
JSON
data JSONB- Store JSON that you can search and index
data->'user'- Get a JSON value
data->>'name'- Get a value as text
data @> '{"a":1}'- Does the JSON contain this?
CREATE INDEX ON t USING GIN (data);- Index for JSON searches
Speed
EXPLAIN ANALYZE SELECT ...- Run the query and show how it was done
Seq Scan / Index Scan- Read every row / use an index
CREATE INDEX CONCURRENTLY ...- Add an index without blocking writes
VACUUM ANALYZE t;- Clean up and refresh the statistics
Backup
pg_dump -Fc dbname > db.dump- Save a database to a file
pg_restore -d dbname db.dump- Load it back
Remember
- Index the columns you filter, join and sort on. Do not index everything: each index slows writes.
- Use TIMESTAMPTZ for times, so every time zone is handled for you.
Learn it properly
A cheat sheet helps you remember. These lessons explain the ideas step by step, with examples.