PostgreSQL Cheat Sheet

A compact reference for PostgreSQL 15+ covering connections, psql meta-commands, SQL patterns, JSON/JSONB, arrays, and administration.

Connection Strings

postgresql://user:password@host:5432/dbname
postgresql://user:password@host:5432/dbname?sslmode=require
postgresql://user@host/dbname?application_name=api&connect_timeout=10

# Environment variables
export PGHOST=localhost PGPORT=5432 PGUSER=app PGPASSWORD=secret PGDATABASE=shop

# Connect with psql
psql "postgresql://app:[email protected]:5432/shop?sslmode=require"
psql -h localhost -U postgres -d shop

Essential psql Meta-Commands

\l                 -- list databases
\c shop            -- connect to database "shop"
\dt                -- list tables in current schema
\dt public.*       -- list tables in public schema
\d users           -- describe table "users" (columns, indexes, constraints)
\d+ users          -- describe with storage, stats, and comments
\du                -- list roles/users
\dn                -- list schemas
\df                -- list functions
\dv                -- list views
\di                -- list indexes
\x                 -- toggle expanded (vertical) output
\timing            -- toggle query timing
\i script.sql      -- execute SQL file
\copy tbl FROM 'data.csv' CSV HEADER  -- client-side COPY
\e                 -- edit query in $EDITOR
\q                 -- quit

Creating Users and Databases

-- Role with login and password
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret' CREATEDB;

-- Superuser (careful)
CREATE ROLE admin WITH LOGIN SUPERUSER PASSWORD 'strong';

-- Database owned by app_user
CREATE DATABASE shop OWNER app_user ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' TEMPLATE template0;

-- Grants
GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

SELECT Patterns

SELECT id, email, created_at
FROM   users
WHERE  status = 'active'
   AND created_at >= NOW() - INTERVAL '30 days'
ORDER  BY created_at DESC
LIMIT  50 OFFSET 0;

-- Keyset (cursor) pagination - faster than OFFSET
SELECT * FROM users
WHERE  (created_at, id) < ($1, $2)
ORDER  BY created_at DESC, id DESC
LIMIT  50;

-- CTE + window function
WITH ranked AS (
  SELECT id, user_id, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
  FROM   orders
)
SELECT * FROM ranked WHERE rn = 1;

Joins

-- INNER: rows matching in both
SELECT u.email, o.total
FROM   users u
JOIN   orders o ON o.user_id = u.id;

-- LEFT: keep all users, NULL for missing orders
SELECT u.email, COUNT(o.id) AS orders
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.email;

-- LATERAL: correlated subquery per row
SELECT u.id, latest.created_at
FROM   users u
LEFT   JOIN LATERAL (
  SELECT created_at FROM orders o
  WHERE  o.user_id = u.id
  ORDER  BY created_at DESC LIMIT 1
) latest ON true;

Aggregation

SELECT date_trunc('day', created_at) AS day,
       COUNT(*)                       AS orders,
       SUM(total)                     AS revenue,
       AVG(total)::numeric(10,2)      AS avg_order,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total) AS p95
FROM   orders
WHERE  created_at >= NOW() - INTERVAL '7 days'
GROUP  BY 1
HAVING COUNT(*) > 10
ORDER  BY 1;

Upsert: INSERT ... ON CONFLICT

-- Do nothing on duplicate key
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Ada')
ON CONFLICT (email) DO NOTHING;

-- Merge: update selected columns, keep created_at
INSERT INTO users (email, name, updated_at)
VALUES ('[email protected]', 'Ada Lovelace', NOW())
ON CONFLICT (email) DO UPDATE
   SET name       = EXCLUDED.name,
       updated_at = EXCLUDED.updated_at
   WHERE users.name IS DISTINCT FROM EXCLUDED.name
RETURNING id, xmax = 0 AS inserted;

-- MERGE (Postgres 15+) for multi-way conditional writes
MERGE INTO inventory i
USING deltas d ON d.sku = i.sku
WHEN MATCHED     THEN UPDATE SET qty = i.qty + d.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (d.sku, d.qty);

JSON and JSONB Operators

-- Storage: prefer JSONB (binary, indexable) over JSON
CREATE TABLE events (id bigserial PRIMARY KEY, payload jsonb NOT NULL);

-- Access
SELECT payload -> 'user'          AS user_json,   -- returns jsonb
       payload ->> 'user'         AS user_text,   -- returns text
       payload #> '{user,name}'    AS name_json,
       payload #>> '{user,name}'   AS name_text
FROM   events;

-- Containment / existence (uses GIN index)
SELECT * FROM events WHERE payload @> '{"type":"signup"}';
SELECT * FROM events WHERE payload ? 'error';

-- Mutation
UPDATE events
SET    payload = jsonb_set(payload, '{user,name}', '"Grace"', true)
WHERE  id = 1;

UPDATE events SET payload = payload - 'debug';           -- remove key
UPDATE events SET payload = payload || '{"v":2}'::jsonb; -- merge

-- Index for containment queries
CREATE INDEX events_payload_gin ON events USING GIN (payload jsonb_path_ops);

Array Functions

CREATE TABLE posts (id serial PRIMARY KEY, tags text[]);

INSERT INTO posts (tags) VALUES (ARRAY['sql','postgres','tips']);

SELECT tags[1]                          AS first_tag,
       array_length(tags, 1)            AS n_tags,
       array_append(tags, 'new')        AS appended,
       array_remove(tags, 'sql')        AS removed,
       array_to_string(tags, ', ')      AS csv
FROM   posts;

-- Contains / overlaps
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];
SELECT * FROM posts WHERE tags && ARRAY['sql','nosql'];

-- Expand to rows
SELECT id, tag FROM posts, UNNEST(tags) AS tag;

-- GIN index for array membership
CREATE INDEX posts_tags_gin ON posts USING GIN (tags);

Administration

-- Reclaim space and update visibility map (non-blocking)
VACUUM (VERBOSE, ANALYZE) orders;

-- Rewrite table + indexes (locks table); prefer pg_repack in prod
VACUUM FULL orders;

-- Refresh planner statistics
ANALYZE users;

-- Query plan with actual timing and buffer usage
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

-- Long-running queries
SELECT pid, now() - query_start AS runtime, state, query
FROM   pg_stat_activity
WHERE  state = 'active' AND now() - query_start > INTERVAL '30 seconds';

-- Cancel / terminate
SELECT pg_cancel_backend(12345);      -- polite
SELECT pg_terminate_backend(12345);   -- forceful

-- Table and index sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM   pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

Backup and Restore

# Logical dump (single DB) - custom format is compressed and parallel-restorable
pg_dump -h db.example.com -U app -d shop -Fc -f shop.dump

# Plain SQL dump
pg_dump -d shop -Fp -f shop.sql

# Schema only / data only
pg_dump -d shop -s -f schema.sql
pg_dump -d shop -a -f data.sql

# All databases + roles
pg_dumpall -h db.example.com -U postgres -f cluster.sql

# Restore custom-format dump with 4 parallel workers
pg_restore -h localhost -U postgres -d shop_new -j 4 --clean --if-exists shop.dump

# Restore plain SQL
psql -d shop_new -f shop.sql

For point-in-time recovery, combine a base backup via pg_basebackup with continuous WAL archiving through archive_command, then replay to a target time with recovery_target_time.