🐘

PostgreSQL Cheatsheet

Queries, indexes, JSONB, transactions, and admin reference

psql CLI

psql -U user -d dbname -h localhost
\l                    -- list databases
\c dbname             -- connect to database
\dt                   -- list tables
\d tablename          -- describe table
\di                   -- list indexes
\df                   -- list functions
\x                    -- toggle expanded output
\timing               -- show query time
\e                    -- open query in editor
\q                    -- quit

Table Operations

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

-- Alter
ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Drop
DROP TABLE users;
DROP TABLE IF EXISTS users CASCADE;

Core Queries

-- SELECT with filtering and sorting
SELECT id, name, email
FROM users
WHERE created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

-- COUNT and GROUP BY
SELECT category, COUNT(*) AS total
FROM posts
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY total DESC;

-- JOIN
SELECT u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.active = true;

-- LEFT JOIN (include users with no posts)
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

Insert, Update, Delete

-- Insert
INSERT INTO users (email, name) VALUES ('x@example.com', 'Alice');

-- Upsert (insert or update on conflict)
INSERT INTO users (email, name)
VALUES ('x@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- Update
UPDATE users SET name = 'Bob', updated_at = NOW()
WHERE id = 42;

-- Delete
DELETE FROM users WHERE id = 42;

-- Returning modified rows
UPDATE users SET active = false WHERE id = 42 RETURNING *;

Indexes

-- B-tree (default, good for =, <, >, BETWEEN, ORDER BY)
CREATE INDEX idx_users_email ON users (email);

-- Partial index (smaller, faster for filtered queries)
CREATE INDEX idx_active_users ON users (created_at) WHERE active = true;

-- Composite index
CREATE INDEX idx_posts_user_date ON posts (user_id, created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- GIN index for JSONB and full-text search
CREATE INDEX idx_metadata ON posts USING GIN (metadata);

-- Check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

-- Drop index concurrently (no lock)
DROP INDEX CONCURRENTLY idx_users_email;

JSONB

-- Create column
ALTER TABLE posts ADD COLUMN metadata JSONB;

-- Insert
INSERT INTO posts (metadata)
VALUES ('{"tags": ["go", "aws"], "views": 100}');

-- Query operators
-- @>  contains
SELECT * FROM posts WHERE metadata @> '{"status": "published"}';

-- ?   key exists
SELECT * FROM posts WHERE metadata ? 'tags';

-- ->> get value as text
SELECT metadata->>'author' FROM posts;

-- #>> get nested value
SELECT metadata#>>'{address,city}' FROM posts;

-- Update nested key
UPDATE posts SET metadata = jsonb_set(metadata, '{views}', '200');

-- GIN index for fast JSONB queries
CREATE INDEX idx_posts_meta ON posts USING GIN (metadata);

Transactions

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

-- Rollback on error
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- something fails
ROLLBACK;

-- Savepoints
BEGIN;
  SAVEPOINT sp1;
  UPDATE ...;
  ROLLBACK TO sp1;   -- undo only to savepoint
COMMIT;

Useful Queries

-- Table sizes
SELECT relname AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Slow queries (requires pg_stat_statements extension)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Lock monitoring
SELECT pid, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

-- Kill a connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();

Loading…

psql CLI

psql -U user -d dbname -h localhost \l -- list databases \c dbname -- connect to database \dt -- list tables \d tablename -- describe table \di -- list indexes \df -- list functions \x -- toggle expanded output \timing -- show query time \e -- open query in editor \q -- quit

Table Operations

-- Create CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Alter ALTER TABLE users ADD COLUMN age INT; ALTER TABLE users DROP COLUMN age; ALTER TABLE users RENAME COLUMN name TO full_name; ALTER TABLE users ALTER COLUMN email SET NOT NULL; -- Drop DROP TABLE users; DROP TABLE IF EXISTS users CASCADE;

Core Queries

-- SELECT with filtering and sorting SELECT id, name, email FROM users WHERE created_at > NOW() - INTERVAL '30 days' ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- COUNT and GROUP BY SELECT category, COUNT(*) AS total FROM posts GROUP BY category HAVING COUNT(*) > 5 ORDER BY total DESC; -- JOIN SELECT u.name, p.title FROM users u JOIN posts p ON p.user_id = u.id WHERE u.active = true; -- LEFT JOIN (include users with no posts) SELECT u.name, COUNT(p.id) AS post_count FROM users u LEFT JOIN posts p ON p.user_id = u.id GROUP BY u.id, u.name;

Insert, Update, Delete

-- Insert INSERT INTO users (email, name) VALUES ('x@example.com', 'Alice'); -- Upsert (insert or update on conflict) INSERT INTO users (email, name) VALUES ('x@example.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; -- Update UPDATE users SET name = 'Bob', updated_at = NOW() WHERE id = 42; -- Delete DELETE FROM users WHERE id = 42; -- Returning modified rows UPDATE users SET active = false WHERE id = 42 RETURNING *;

Indexes

-- B-tree (default, good for =, <, >, BETWEEN, ORDER BY) CREATE INDEX idx_users_email ON users (email); -- Partial index (smaller, faster for filtered queries) CREATE INDEX idx_active_users ON users (created_at) WHERE active = true; -- Composite index CREATE INDEX idx_posts_user_date ON posts (user_id, created_at DESC); -- Unique index CREATE UNIQUE INDEX idx_users_email_unique ON users (email); -- GIN index for JSONB and full-text search CREATE INDEX idx_metadata ON posts USING GIN (metadata); -- Check index usage SELECT * FROM pg_stat_user_indexes WHERE relname = 'users'; -- Drop index concurrently (no lock) DROP INDEX CONCURRENTLY idx_users_email;

JSONB

-- Create column ALTER TABLE posts ADD COLUMN metadata JSONB; -- Insert INSERT INTO posts (metadata) VALUES ('{"tags": ["go", "aws"], "views": 100}'); -- Query operators -- @> contains SELECT * FROM posts WHERE metadata @> '{"status": "published"}'; -- ? key exists SELECT * FROM posts WHERE metadata ? 'tags'; -- ->> get value as text SELECT metadata->>'author' FROM posts; -- #>> get nested value SELECT metadata#>>'{address,city}' FROM posts; -- Update nested key UPDATE posts SET metadata = jsonb_set(metadata, '{views}', '200'); -- GIN index for fast JSONB queries CREATE INDEX idx_posts_meta ON posts USING GIN (metadata);

Transactions

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- Rollback on error BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- something fails ROLLBACK; -- Savepoints BEGIN; SAVEPOINT sp1; UPDATE ...; ROLLBACK TO sp1; -- undo only to savepoint COMMIT;

Useful Queries

-- Table sizes SELECT relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; -- Slow queries (requires pg_stat_statements extension) SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; -- Lock monitoring SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event IS NOT NULL; -- Kill a connection SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb' AND pid <> pg_backend_pid();