Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

PostgreSQL Cheat Sheet

psql

// connect and run files
psql -h localhost -U postgres -d mydb
psql -d mydb -f schema.sql
pg_dump mydb > backup.sql
psql -d mydb < backup.sql

Meta-commands

\l   \c dbname   \dt   \d table   \du   \x   \timing   \i file.sql   \q

Tables

CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, tags TEXT[] DEFAULT '{}', meta JSONB DEFAULT '{}', created_at TIMESTAMPTZ DEFAULT now());
ALTER TABLE t ADD COLUMN email TEXT UNIQUE; ALTER TABLE t ALTER COLUMN name SET NOT NULL;
CREATE INDEX ON t (lower(email)); CREATE INDEX ON t USING GIN (meta);

Queries

SELECT * FROM t WHERE name ILIKE 'a%' ORDER BY id DESC LIMIT 10 OFFSET 20;
SELECT DISTINCT ON (city) city, name FROM users ORDER BY city, joined_at;
SELECT level, count(*) FILTER (WHERE price = 0) FROM courses GROUP BY level;
SELECT string_agg(name, ', ' ORDER BY name) FROM users;
WITH x AS (SELECT ...) SELECT ...; WITH RECURSIVE r AS (...) SELECT ...;
SELECT rank() OVER (PARTITION BY a ORDER BY b DESC) FROM t;

Writes

INSERT INTO t (name) VALUES ('x') RETURNING id;
INSERT INTO t (id, n) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET n = EXCLUDED.n;
UPDATE t SET n = n + 1 WHERE id = 1 RETURNING *;
BEGIN; SAVEPOINT s; ROLLBACK TO s; COMMIT;

JSONB and arrays

meta->'a'; meta->>'a'; meta @> '{"a": 1}'; meta ? 'key'; jsonb_set(meta, '{a}', '2');
'x' = ANY (tags); tags && ARRAY['a','b']; array_append(tags, 'z'); unnest(tags); array_length(tags, 1);

Dates and casts

now(); CURRENT_DATE; date_trunc('month', ts); ts + INTERVAL '7 days'; age(a, b);
to_char(d, 'DD Mon YYYY'); EXTRACT(YEAR FROM d); '42'::int; x::numeric(10,2);
generate_series(1, 10); gen_random_uuid();

Full PostgreSQL documentation