PostgreSQL Reference

e.g. psql -h localhost -p 5432 -U postgres -d myapp

e.g. psql postgres://admin:secret@db.example.com:5432/production

e.g. createdb -U postgres myapp

e.g. dropdb -U postgres old_database

e.g. \c myapp

e.g. \d users

e.g. \i /path/to/migration.sql

e.g. id SERIAL PRIMARY KEY

e.g. age INTEGER NOT NULL

e.g. price NUMERIC(10, 2)

e.g. name TEXT NOT NULL

e.g. is_active BOOLEAN DEFAULT true

e.g. created_at TIMESTAMPTZ DEFAULT NOW()

e.g. birth_date DATE login_time TIME retention INTERVAL '30 days'

e.g. id UUID DEFAULT gen_random_uuid() PRIMARY KEY

e.g. metadata JSONB DEFAULT '{}'::jsonb

e.g. raw_payload JSON

e.g. tags TEXT[] DEFAULT '{}'

e.g. CREATE TYPE status AS ENUM ('active', 'inactive', 'banned'); user_status status DEFAULT 'active'

e.g. avatar BYTEA

e.g. client_ip INET

e.g. search_vector TSVECTOR

e.g. CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() );

e.g. CREATE TABLE users_backup (LIKE users INCLUDING ALL);

e.g. ALTER TABLE users ADD COLUMN phone TEXT;

e.g. ALTER TABLE users DROP COLUMN phone;

e.g. ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

e.g. ALTER TABLE users RENAME COLUMN name TO full_name;

e.g. ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

e.g. DROP TABLE IF EXISTS temp_data CASCADE;

e.g. TRUNCATE TABLE logs RESTART IDENTITY CASCADE;

e.g. id BIGSERIAL PRIMARY KEY

e.g. email TEXT UNIQUE NOT NULL

e.g. created_at TIMESTAMPTZ DEFAULT NOW()

e.g. age INTEGER CHECK (age >= 0 AND age <= 150)

e.g. user_id BIGINT REFERENCES users(id) ON DELETE CASCADE

e.g. INSERT INTO users (email, name) VALUES ('user@example.com', 'Alice');

e.g. INSERT INTO users (email, name) VALUES ('bob@test.com', 'Bob') RETURNING id, created_at;

e.g. INSERT INTO users_archive SELECT * FROM users WHERE created_at < '2024-01-01';

e.g. INSERT INTO users (email, name) VALUES ('user@test.com', 'New Name') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

e.g. INSERT INTO tags (name) VALUES ('rust') ON CONFLICT (name) DO NOTHING;

e.g. SELECT id, name, email FROM users WHERE is_active = true ORDER BY created_at DESC;

e.g. SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC;

e.g. UPDATE users SET name = 'Alice Smith' WHERE id = 1;

e.g. UPDATE orders SET status = 'shipped' FROM shipments WHERE orders.id = shipments.order_id AND shipments.shipped_at IS NOT NULL;

e.g. UPDATE users SET is_active = false WHERE last_login < NOW() - INTERVAL '1 year' RETURNING id, email;

e.g. DELETE FROM sessions WHERE expires_at < NOW();

e.g. DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.is_banned = true;

e.g. DELETE FROM expired_tokens WHERE expires_at < NOW() RETURNING token_id;

e.g. CREATE INDEX idx_users_email ON users (email);

e.g. CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

e.g. CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

e.g. CREATE INDEX idx_locations_point ON locations USING GiST (coordinates);

e.g. CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

e.g. CREATE INDEX idx_active_users ON users (email) WHERE is_active = true;

e.g. CREATE INDEX idx_users_lower_email ON users (LOWER(email));

e.g. CREATE INDEX idx_orders_user_status ON orders (user_id, status);

e.g. CREATE INDEX CONCURRENTLY idx_orders_date ON orders (created_at);

e.g. DROP INDEX IF EXISTS idx_users_email;

e.g. REINDEX INDEX CONCURRENTLY idx_users_email;

e.g. SELECT metadata -> 'address' FROM users;

e.g. SELECT metadata ->> 'city' FROM users WHERE metadata ->> 'city' = 'Vienna';

e.g. SELECT tags -> 0 FROM posts;

e.g. SELECT metadata #> '{address,city}' FROM users;

e.g. SELECT metadata #>> '{address,zip}' FROM users;

e.g. SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

e.g. SELECT * FROM users WHERE metadata <@ '{"role": "admin", "level": 5}';

e.g. SELECT * FROM users WHERE metadata ? 'phone';

e.g. SELECT * FROM users WHERE metadata ?| array['phone', 'mobile'];

e.g. SELECT * FROM users WHERE metadata ?& array['email', 'phone'];

e.g. UPDATE users SET metadata = jsonb_set(metadata, '{address,city}', '"Berlin"');

e.g. UPDATE users SET metadata = metadata || '{"verified": true}'::jsonb;

e.g. UPDATE users SET metadata = metadata - 'temp_field';

e.g. SELECT user_id, jsonb_agg(tag) AS tags FROM user_tags GROUP BY user_id;

e.g. SELECT jsonb_object_agg(key, value) FROM settings;

e.g. SELECT key, value FROM users, jsonb_each(metadata) WHERE id = 1;

e.g. SELECT jsonb_array_elements(tags) AS tag FROM posts WHERE id = 1;

e.g. SELECT jsonb_typeof(metadata -> 'score') FROM users;

e.g. SELECT ARRAY['postgres', 'mysql', 'sqlite'];

e.g. SELECT tags[1] FROM posts;

e.g. SELECT tags[1:3] FROM posts;

e.g. SELECT array_length(tags, 1) FROM posts;

e.g. SELECT * FROM posts WHERE 'rust' = ANY(tags);

e.g. SELECT * FROM scores WHERE 80 <= ALL(grades);

e.g. SELECT user_id, array_agg(tag ORDER BY tag) FROM user_tags GROUP BY user_id;

e.g. SELECT unnest(tags) AS tag FROM posts WHERE id = 1;

e.g. SELECT * FROM posts WHERE tags @> ARRAY['rust', 'async'];

e.g. SELECT * FROM posts WHERE tags && ARRAY['rust', 'go', 'python'];

e.g. SELECT array_cat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}

e.g. UPDATE posts SET tags = array_append(tags, 'new-tag') WHERE id = 1;

e.g. UPDATE posts SET tags = array_remove(tags, 'deprecated') WHERE id = 1;

e.g. SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

e.g. SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM students;

e.g. SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) FROM students;

e.g. SELECT date, revenue, revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change FROM daily_sales;

e.g. SELECT date, temperature, LEAD(temperature) OVER (ORDER BY date) AS next_day_temp FROM weather;

e.g. SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions;

e.g. SELECT date, price, AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d FROM stock_prices;

e.g. SELECT name, department, salary, FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner FROM employees;

e.g. SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

e.g. SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS percentile FROM employees;

e.g. WITH active_users AS ( SELECT * FROM users WHERE is_active = true ) SELECT * FROM active_users WHERE created_at > '2024-01-01';

e.g. WITH user_orders AS ( SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id ), top_users AS ( SELECT * FROM user_orders WHERE order_count > 10 ) SELECT u.name, t.order_count FROM users u JOIN top_users t ON u.id = t.user_id;

e.g. WITH RECURSIVE org_tree AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, t.depth + 1 FROM employees e JOIN org_tree t ON e.manager_id = t.id ) SELECT * FROM org_tree ORDER BY depth;

e.g. WITH deleted AS ( DELETE FROM sessions WHERE expires_at < NOW() RETURNING user_id ) SELECT COUNT(DISTINCT user_id) AS affected_users FROM deleted;

e.g. WITH RECURSIVE graph AS ( SELECT id, parent_id, ARRAY[id] AS path FROM nodes WHERE id = 1 UNION ALL SELECT n.id, n.parent_id, g.path || n.id FROM nodes n JOIN graph g ON n.parent_id = g.id WHERE n.id != ALL(g.path) -- cycle detection ) SELECT * FROM graph;

e.g. WITH RECURSIVE dates AS ( SELECT DATE '2024-01-01' AS d UNION ALL SELECT d + 1 FROM dates WHERE d < '2024-12-31' ) SELECT d FROM dates;

e.g. SELECT to_tsvector('english', 'The quick brown foxes jumped');

e.g. SELECT to_tsquery('english', 'quick & fox');

e.g. SELECT plainto_tsquery('english', 'quick brown fox');

e.g. SELECT websearch_to_tsquery('english', '"quick fox" -lazy');

e.g. SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & performance');

e.g. SELECT title, ts_rank(search_vector, query) AS rank FROM articles, to_tsquery('english', 'postgres') AS query WHERE search_vector @@ query ORDER BY rank DESC;

e.g. SELECT ts_headline('english', body, to_tsquery('english', 'database'), 'StartSel=<b>, StopSel=</b>, MaxWords=50') FROM articles;

e.g. ALTER TABLE articles ADD COLUMN search_vector TSVECTOR; UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || body); CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

e.g. CREATE TRIGGER articles_search_update BEFORE INSERT OR UPDATE ON articles FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

e.g. CREATE ROLE app_user WITH LOGIN PASSWORD 'secret123';

e.g. CREATE ROLE readonly;

e.g. GRANT readonly TO app_user;

e.g. GRANT SELECT, INSERT, UPDATE ON users TO app_user;

e.g. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

e.g. REVOKE DELETE ON users FROM app_user;

e.g. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

e.g. ALTER TABLE documents ENABLE ROW LEVEL SECURITY; CREATE POLICY user_docs ON documents FOR ALL TO app_user USING (owner_id = current_setting('app.user_id')::int);

e.g. CREATE EXTENSION IF NOT EXISTS pg_trgm;

e.g. CREATE EXTENSION pg_trgm; CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops); SELECT * FROM users WHERE name % 'Jonh'; -- fuzzy match

e.g. SELECT gen_random_uuid(); -- built-in, no extension needed in PG13+

e.g. CREATE EXTENSION postgis; SELECT ST_Distance( ST_MakePoint(16.3738, 48.2082)::geography, -- Vienna ST_MakePoint(13.4050, 52.5200)::geography -- Berlin ) / 1000 AS km;

e.g. CREATE EXTENSION pg_stat_statements; SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

e.g. CREATE EXTENSION citext; CREATE TABLE users (email CITEXT UNIQUE);

e.g. EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';

e.g. EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@test.com';

e.g. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM orders WHERE user_id = 42;

e.g. VACUUM VERBOSE users;

e.g. VACUUM FULL users;

e.g. ANALYZE users;

e.g. VACUUM ANALYZE orders;

e.g. SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_analyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

e.g. SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

e.g. SELECT pg_size_pretty(pg_total_relation_size('users'));

e.g. SELECT pid, state, query, query_start, NOW() - query_start AS duration FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;

e.g. SELECT pg_cancel_backend(12345); -- graceful SELECT pg_terminate_backend(12345); -- force kill

e.g. pg_dump -U postgres myapp > myapp_backup.sql

e.g. pg_dump -U postgres -Fc myapp > myapp_backup.dump

e.g. pg_dump -U postgres -Fd -j 4 myapp -f myapp_backup/

e.g. pg_dump -U postgres -t users -t orders myapp > tables_backup.sql

e.g. pg_dump -U postgres --schema-only myapp > schema.sql

e.g. pg_dump -U postgres --data-only myapp > data.sql

e.g. pg_dumpall -U postgres > full_backup.sql

e.g. psql -U postgres myapp < myapp_backup.sql

e.g. pg_restore -U postgres -d myapp -j 4 myapp_backup.dump

e.g. pg_restore -U postgres --clean --if-exists -d myapp myapp_backup.dump

e.g. pg_restore -l myapp_backup.dump | grep users

e.g. COPY users TO '/tmp/users.csv' WITH (FORMAT csv, HEADER); COPY users FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER);