Skip to content
Tutorials Center
My learning

Topics

Lesson 90 of 90 ยท Practice and Review

PostgreSQL Cheat Sheet

A one-page PostgreSQL cheat sheet: psql commands, data types, operators, DDL and DML patterns, index choices and admin queries, with links to the full lessons.

This page squeezes the PostgreSQL track onto one scrollable reference: the psql commands, types, operators, statements and admin queries you reach for every day, in short snippets you can scan in seconds. Keep it open next to the exercises and the interview questions, while you work, or the night before an interview.

A cheat sheet has no room for reasoning, so every section links to the lesson that explains the topic properly. As in the rest of this track, the editors run SQLite in your browser, so only the portable patterns have live examples. Everything that needs a PostgreSQL server (psql, jsonb operators, timestamptz, index types, pg_catalog queries) is shown as read-only code.

One page, six areas

psql Connect, inspect, script.
Types Pick the right column type.
Operators jsonb, arrays, ranges, text.
DDL and DML Create, change, upsert.
1 / 2

psql

Connect and run things (see installing and connecting and psql essentials):

Terminal
psql "postgresql://user:pass@host:5432/dbname?sslmode=require"
psql -h localhost -U app -d shop -f schema.sql      # run a script
psql -d shop -c "SELECT now();"                     # run one command
psql -X -v ON_ERROR_STOP=1 -f migrate.sql           # skip .psqlrc, stop on first error

Meta-commands (they start with a backslash and are handled by psql, not the server):

Command Does
\l List databases
\c dbname Connect to another database
\dn List schemas
\dt, \dt+ List tables (with sizes)
\d tablename, \d+ tablename Describe a table: columns, indexes, constraints, triggers
\di, \dv, \dm, \ds List indexes, views, materialized views, sequences
\df, \dx, \du List functions, extensions, roles
\x auto Expanded output when rows are too wide
\timing Show how long each statement takes
\e Edit the last statement in your editor
\watch 2 Re-run the last statement every 2 seconds
\copy t FROM 'f.csv' CSV HEADER Client-side CSV import (TO exports)
\i file.sql Run a script from inside psql
\set name value, :name Variables
\q Quit

Data types

Need Use Notes
Whole numbers smallint, integer, bigint 2, 4 and 8 bytes; use bigint for ids that may grow
Exact decimals numeric(p,s) Money and measurements that must not drift; never real or money for accounting
Approximate real, double precision Fast, inexact: 0.1 + 0.2 <> 0.3
Text text Same storage as varchar(n); add CHECK for limits
Yes/no boolean true, false, NULL (three-valued logic)
Limited set enum, or a lookup table Enum values can be added but not easily removed
Date only date
Instant in time timestamptz Stored as UTC, shown in the session zone
Wall-clock time timestamp, time No zone
Duration interval interval '1 month 2 days'
Surrogate key bigint GENERATED ALWAYS AS IDENTITY, or uuid gen_random_uuid(); UUIDv7 for time-ordered keys on 18+
Document jsonb Binary, indexable, no key order or duplicates
List of values type[] integer[], text[]
Interval of values int4range, tstzrange, ... With &&, @> and exclusion constraints
Network inet, cidr
Full-text tsvector, tsquery

Casting and literals:

SQL
SELECT '42'::int, CAST('2025-03-01' AS date), now()::date, 3.7::int;   -- 3.7::int rounds to 4
SELECT '{1,2,3}'::int[], ARRAY[1,2,3], '[2025-01-01,2025-02-01)'::daterange;
SELECT interval '90 minutes', date '2025-03-01' + 30, now() - interval '1 day';

See numeric types, text types, dates and times, UUIDs and identity and range types.

Operators and functions

SQL
-- NULL-aware comparison
a IS DISTINCT FROM b            -- like <>, but NULL-safe
COALESCE(x, 0)                  -- first non-NULL
NULLIF(x, 0)                    -- NULL if x equals 0 (avoids division by zero)

-- Text
'a' || 'b'                      -- concatenate
name ILIKE 'ann%'               -- case-insensitive LIKE
name ~ '^A.*n$'                 -- regex match ( ~* case-insensitive, !~ negated )
regexp_replace(s, '\s+', ' ', 'g')
split_part('a,b,c', ',', 2)     -- 'b'
format('%I = %L', col, val)     -- safely quoted identifier and literal

-- jsonb
doc -> 'key'    doc ->> 'key'   doc #>> '{a,b}'     -- json / text / path as text
doc @> '{"color":"red"}'                              -- containment (GIN-indexable)
doc ? 'key'                                           -- key exists
jsonb_set(doc, '{n}', '5')   jsonb_build_object('a', 1)

-- Arrays
tags @> ARRAY['sql']   tags && ARRAY['a','b']   5 = ANY(nums)
unnest(tags)   array_agg(x ORDER BY x)   cardinality(tags)

-- Ranges
r @> value     r1 && r2     lower(r)   upper(r)   isempty(r)

-- Dates
date_trunc('month', ts)   extract(year FROM ts)   age(ts)   ts AT TIME ZONE 'Europe/Paris'

-- Series
generate_series(1, 10)   generate_series(d1, d2, interval '1 day')

See pattern matching, JSONB basics, arrays and set-returning functions.

Queries

SQL
-- Top-N per group
SELECT DISTINCT ON (genre) genre, title, price
FROM books ORDER BY genre, price DESC;                -- PostgreSQL only

-- Conditional aggregates
SELECT count(*) FILTER (WHERE stock = 0) AS sold_out FROM books;

-- Subtotals
SELECT genre, status, count(*) FROM books GROUP BY ROLLUP (genre, status);   -- CUBE, GROUPING SETS too

-- Correlated subquery in FROM
SELECT c.name, o.* FROM customers c
CROSS JOIN LATERAL (SELECT * FROM orders WHERE customer_id = c.id ORDER BY ordered_on DESC LIMIT 3) o;

-- Ties and paging
SELECT * FROM books ORDER BY price DESC FETCH FIRST 3 ROWS WITH TIES;
SELECT * FROM events WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT 50;   -- keyset paging

-- CTEs
WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1 FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

See DISTINCT ON, FILTER and grouping sets, LATERAL, window functions and CTEs.

The portable core of these patterns runs live. Change the data and predict each result before you press Run:

SQL NULL handling, RETURNING, upsert and FILTER

Press Run (or Ctrl + Enter) to see the result here.

DDL and constraints

SQL
CREATE TABLE booking (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id     bigint NOT NULL REFERENCES room (id) ON DELETE RESTRICT,
  guest       text   NOT NULL CHECK (length(guest) > 0),
  period      tstzrange NOT NULL,
  status      text   NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','paid','cancelled')),
  created_at  timestamptz NOT NULL DEFAULT now(),
  EXCLUDE USING gist (room_id WITH =, period WITH &&)      -- needs btree_gist for the = on room_id
);

ALTER TABLE booking ADD COLUMN note text;                   -- fast, metadata only
ALTER TABLE booking ADD CONSTRAINT paid_has_guest CHECK (status <> 'paid' OR guest <> '') NOT VALID;
ALTER TABLE booking VALIDATE CONSTRAINT paid_has_guest;     -- later, with a lighter lock
ALTER TABLE booking ALTER COLUMN note SET NOT NULL;
DROP TABLE IF EXISTS tmp_import;

CREATE VIEW open_bookings AS SELECT * FROM booking WHERE status = 'pending';
CREATE MATERIALIZED VIEW daily_totals AS SELECT ... ;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_totals;        -- needs a unique index on the view

Foreign key actions: NO ACTION (default), RESTRICT, CASCADE, SET NULL, SET DEFAULT. See designing tables, constraints in depth, exclusion constraints, views and schema migrations.

Safe schema changes on a busy table:

SQL
SET lock_timeout = '3s';                                     -- fail rather than queue behind a long query
CREATE INDEX CONCURRENTLY idx_booking_room ON booking (room_id);
DROP INDEX CONCURRENTLY idx_old;
REINDEX INDEX CONCURRENTLY idx_booking_room;

Transactions and locks

SQL
BEGIN;
SAVEPOINT before_risky;
-- ...
ROLLBACK TO SAVEPOINT before_risky;
COMMIT;                                                     -- or ROLLBACK

BEGIN ISOLATION LEVEL SERIALIZABLE;                         -- retry on SQLSTATE 40001

SELECT * FROM jobs WHERE status = 'queued'
ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;                -- queue workers
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;      -- error instead of waiting
SELECT pg_advisory_xact_lock(42);                           -- app-level lock until the transaction ends
Level Snapshot Prevents
Read Committed (default) Per statement Dirty reads
Repeatable Read Per transaction Also non-repeatable reads and phantoms
Serializable Per transaction, with conflict detection Also write skew and other anomalies

A transaction that hits an error stays aborted until ROLLBACK. See transactions, isolation levels, locking and MVCC.

Transactions work the same way in SQLite, so the shape runs live. The CHECK constraint rejects overdrafts, and the rollback undoes the first transfer:

SQL BEGIN, ROLLBACK and COMMIT

Press Run (or Ctrl + Enter) to see the result here.

Choosing an index

Query shape Index
=, <, >, BETWEEN, ORDER BY, LIKE 'abc%' (with the right opclass or C collation) B-tree (default)
Equality and range on several columns B-tree with equality columns first, then the range or sort column
Same query, one rare state Partial: CREATE INDEX ... WHERE status = 'open'
lower(email) = ... Expression: CREATE INDEX ... (lower(email))
Avoid visiting the table INCLUDE (cols) for index-only scans
jsonb @>, array @> / &&, full text @@ GIN
Ranges, geometry, nearest neighbour, exclusion constraints GiST
LIKE '%x%', similarity (with pg_trgm) GIN or GiST with gin_trgm_ops
Huge append-only table, queries by time range BRIN
Equality only, very large keys Hash (rarely better than B-tree)
Tries, points, quad-trees SP-GiST
Embedding similarity (pgvector) HNSW or IVFFlat
SQL
CREATE INDEX idx_a ON t (tenant_id, created_at DESC);
CREATE UNIQUE INDEX idx_b ON users (lower(email)) WHERE deleted_at IS NULL;
CREATE INDEX idx_c ON docs USING gin (body jsonb_path_ops);
CREATE INDEX idx_d ON logs USING brin (logged_at);

See how indexes work, B-tree, partial and expression indexes and GIN and GiST.

A partial index is one more portable feature, so you can watch the planner use it:

SQL A partial index in the query plan

Press Run (or Ctrl + Enter) to see the result here.

Performance

SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;     -- actually runs it; wrap writes in BEGIN ... ROLLBACK
ANALYZE tablename;                         -- refresh planner statistics
VACUUM (VERBOSE, ANALYZE) tablename;

What to look at in a plan: estimated versus actual rows, the node with most of the time, Seq Scan on big tables, Sort Method: external merge (spilled to disk, raise work_mem), Rows Removed by Filter, Buffers: read versus hit. See EXPLAIN, reading plans and query tuning patterns.

Typical starting points for settings, on a dedicated server: shared_buffers about 25% of RAM, effective_cache_size about 50 to 75%, work_mem a few MB to tens of MB (per sort or hash node), maintenance_work_mem several hundred MB, max_connections modest with a pooler in front. Treat them as starting values to measure, not rules. See configuration tuning.

Admin queries

SQL
-- Who is running what, longest first
SELECT pid, usename, state, now() - query_start AS running, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running DESC;

SELECT pg_cancel_backend(1234);        -- cancel the query
SELECT pg_terminate_backend(1234);     -- end the connection

-- Sizes
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total
FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

-- Dead tuples and last vacuum
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- Indexes that are never read
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0;

-- Who is blocking whom
SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Cache hit ratio per database
SELECT datname, round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database;

-- Replication lag (on a primary)
SELECT client_addr, state, replay_lag FROM pg_stat_replication;

See monitoring and catalogs, VACUUM, index maintenance and pg_stat_statements.

Security and operations

SQL
CREATE ROLE app LOGIN PASSWORD '...';                       -- password: use a secret store, never source control
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoice
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
Terminal
pg_dump -Fc -d shop -f shop.dump               # custom-format logical backup
pg_restore -d shop_copy --clean --if-exists shop.dump
pg_basebackup -D /backup/base -Fp -Xs -P       # physical base backup (add WAL archiving for PITR)

Always pass user input as parameters ($1, %s in drivers), never by string concatenation; in dynamic SQL use format('%I', ...) for identifiers and %L for literals. See roles and privileges, row-level security, backup and recovery and SQL injection.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which expression compares two values and treats NULL as an ordinary value?

    IS DISTINCT FROM (and IS NOT DISTINCT FROM) are NULL-safe comparisons. With = or <>, a NULL operand produces unknown.
  2. 2. Which index type is the right choice for a jsonb containment query such as doc @> '{"color":"red"}'?

    GIN indexes the keys and values inside a document, which is what containment needs. A B-tree can only order whole values.
  3. 3. How do you create an index on a busy table without blocking writes?

    CONCURRENTLY builds the index without blocking writes, at the price of a slower build, and it cannot run inside a transaction block.
  4. 4. What does EXPLAIN (ANALYZE) do that plain EXPLAIN does not?

    ANALYZE really runs the query, so an UPDATE or DELETE changes data. Wrap such tests in BEGIN and ROLLBACK.
  5. 5. Which snippet makes a query safe from SQL injection?

    Parameters keep data separate from the SQL text, so input can never change the statement's structure.

Summary

  • psql meta-commands (\d+, \x, \timing, \copy) inspect and script; write SQL files and run them with -f and ON_ERROR_STOP.
  • Prefer bigint identity or uuid keys, numeric for exact values, text, timestamptz and jsonb.
  • Know the operator families: ->> and @> for jsonb, ANY and && for arrays, @> and && for ranges, ILIKE and ~ for text.
  • ON CONFLICT ... RETURNING, FILTER, DISTINCT ON, LATERAL and keyset paging cover most everyday query needs.
  • Match the index to the query: B-tree by default, partial and expression indexes for rare or computed conditions, GIN, GiST and BRIN for specialised data.
  • Change big tables safely with lock_timeout, NOT VALID plus VALIDATE, and CONCURRENTLY.
  • Use pg_stat_activity, pg_stat_user_tables and pg_locks to see what the server is doing, and keep roles least-privileged.

This was the last lesson in the PostgreSQL track. To keep going, revisit a weak area through the exercises, or start from What Is PostgreSQL? to follow the full path again.