\l              list databases
\c dbname       connect to a database
\dt             list tables
\d tablename    describe a table (columns, indexes, constraints)
\dn             list schemas
\du             list roles/users
\dv             list views
\df             list functions
\x              toggle expanded (vertical) output
\timing         toggle query timing
\i file.sql     run commands from a file
\q              quit

INTEGER, BIGINT, SMALLINT      -- whole numbers
NUMERIC(p, s), DECIMAL(p, s)   -- exact fixed-precision numbers
REAL, DOUBLE PRECISION         -- floating point
TEXT, VARCHAR(n), CHAR(n)      -- text
BOOLEAN                        -- true/false/null
DATE, TIME, TIMESTAMP, TIMESTAMPTZ
UUID
JSON, JSONB
ARRAY (e.g. INTEGER[])
BYTEA                          -- binary data

SELECT col1, col2 FROM table_name WHERE condition;
SELECT * FROM table_name ORDER BY col DESC LIMIT 10 OFFSET 20;
SELECT DISTINCT col FROM table_name;
SELECT * FROM table_name WHERE col ILIKE '%pattern%';
SELECT * FROM table_name WHERE col IN (1, 2, 3);
SELECT * FROM table_name WHERE col BETWEEN 10 AND 20;
SELECT * FROM table_name WHERE col IS NULL;

SELECT * FROM a JOIN b ON a.id = b.a_id;            -- INNER JOIN
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;        -- keeps unmatched a rows
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;       -- keeps unmatched b rows
SELECT * FROM a FULL JOIN b ON a.id = b.a_id;        -- keeps unmatched from both
SELECT * FROM a CROSS JOIN b;                        -- cartesian product

SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount)
FROM orders;

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

col -> 'key'        -- get JSON object field (returns jsonb)
col ->> 'key'       -- get JSON object field as text
col -> 0            -- get array element by index (returns jsonb)
col ->> 0           -- get array element as text
col #> '{a,b}'      -- get nested field by path (returns jsonb)
col #>> '{a,b}'     -- get nested field by path as text
col @> '{"a":1}'    -- does left contain right?
col <@ '{"a":1}'    -- is left contained by right?
col ? 'key'         -- does top-level key/element exist?
col - 'key'         -- delete key from object
col || '{"a":1}'    -- concatenate/merge jsonb

ARRAY[1, 2, 3]              -- array literal
col @> ARRAY[1, 2]          -- does col contain these elements?
col && ARRAY[1, 2]          -- do arrays overlap?
1 = ANY(col)                -- is 1 in the array?
array_length(col, 1)        -- length of a 1-D array
unnest(col)                 -- expand array to rows

SELECT
  customer_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS rn,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
  SUM(amount)  OVER (PARTITION BY customer_id) AS customer_total,
  LAG(amount)  OVER (ORDER BY created_at) AS prev_amount
FROM orders;

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- or ROLLBACK;

SAVEPOINT sp1;
  -- ... more statements ...
ROLLBACK TO SAVEPOINT sp1;

CREATE INDEX idx_name ON table_name (column);
CREATE UNIQUE INDEX idx_name ON table_name (column);
CREATE INDEX idx_name ON table_name USING GIN (jsonb_column);
CREATE INDEX idx_name ON table_name (column) WHERE status = 'active'; -- partial
CREATE INDEX idx_name ON table_name (lower(column));                  -- expression
DROP INDEX idx_name;
EXPLAIN ANALYZE SELECT ...;  -- verify an index is actually used