PostgreSQL Cheat Sheet: Commands, Queries & Admin Tips

PostgreSQL is the most feature-rich open-source relational database. This reference covers everything from psql basics to JSONB and full-text search — copy-ready for daily work.

Quick reference

The 25 patterns that cover 90% of daily Postgres work.

psql client

Connecting

# Local connection
psql -U postgres -d mydb

# Remote connection
psql -h hostname -p 5432 -U user -d dbname

# Connection string (URL)
psql "postgresql://user:pass@host:5432/dbname"

# Run single query and exit
psql -U postgres -d mydb -c "SELECT count(*) FROM users;"

# Run a SQL file
psql -U postgres -d mydb -f schema.sql

Essential meta-commands

\l             -- list databases
\l+            -- list databases with sizes
\c dbname      -- connect to database
\dt            -- list tables (current schema)
\dt schema.*   -- list tables in schema
\d tablename   -- describe table (columns, types, indexes)
\d+ tablename  -- describe table with extra detail
\di            -- list indexes
\dv            -- list views
\ds            -- list sequences
\df            -- list functions
\dn            -- list schemas
\du            -- list roles
\timing        -- toggle query timing
\x             -- toggle expanded (vertical) output
\x auto        -- expanded only when needed
\e             -- open current query in $EDITOR
\i file.sql    -- run a SQL file
\o file.txt    -- redirect output to file
\q             -- quit

Data types

Common types

Type casting

-- Cast syntax
SELECT '42'::INTEGER;
SELECT 3.14::TEXT;
SELECT CAST('2024-01-15' AS DATE);

-- Common casts
SELECT now()::DATE;                    -- timestamp → date
SELECT 42::NUMERIC(10,2);             -- integer → decimal
SELECT 'true'::BOOLEAN;              -- text → boolean
SELECT '{"a":1}'::JSONB;             -- text → jsonb
SELECT '{1,2,3}'::INTEGER[];         -- text → array

DDL — Creating and modifying tables

CREATE TABLE

CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  role        TEXT NOT NULL DEFAULT 'user'
               CHECK (role IN ('user', 'admin', 'moderator')),
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- With foreign key
CREATE TABLE posts (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title      TEXT NOT NULL,
  body       TEXT,
  published  BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE

-- Add column
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- Drop column
ALTER TABLE users DROP COLUMN avatar_url;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Change type
ALTER TABLE users ALTER COLUMN metadata TYPE JSONB USING metadata::JSONB;

-- Set default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'user';

-- Drop default
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT users_email_check;

-- Rename table
ALTER TABLE users RENAME TO app_users;

Indexes

Creating indexes

-- Basic index
CREATE INDEX idx_users_email ON users(email);

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

-- Multi-column index
CREATE INDEX idx_posts_user_published ON posts(user_id, published);

-- Partial index (indexes only matching rows — much smaller)
CREATE INDEX idx_posts_published ON posts(created_at)
  WHERE published = TRUE;

-- Functional/expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- JSONB GIN index (for @>, ?, ?|, ?& operators)
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Full-text search index
CREATE INDEX idx_posts_fts ON posts USING GIN(to_tsvector('english', title || ' ' || body));

-- Without table lock (production safe)
CREATE INDEX CONCURRENTLY idx_posts_created ON posts(created_at);

Index tips

Querying

Basic SELECT

-- Filter and sort
SELECT id, email, created_at
FROM users
WHERE role = 'admin' AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

-- Count with filter
SELECT COUNT(*) FROM users WHERE role = 'admin';

-- Distinct values
SELECT DISTINCT role FROM users ORDER BY role;

-- Conditional expression
SELECT
  id,
  email,
  CASE
    WHEN role = 'admin' THEN 'Administrator'
    WHEN role = 'moderator' THEN 'Mod'
    ELSE 'Regular user'
  END AS role_label
FROM users;

JOINs

-- INNER JOIN — only matching rows
SELECT u.email, p.title
FROM users u
INNER JOIN posts p ON p.user_id = u.id;

-- LEFT JOIN — all left rows, NULL for non-matching right
SELECT u.email, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.email;

-- Multiple joins
SELECT u.email, p.title, c.body AS comment
FROM users u
JOIN posts p ON p.user_id = u.id
JOIN comments c ON c.post_id = p.id
WHERE p.published = TRUE;

Aggregation

SELECT
  role,
  COUNT(*)           AS total,
  COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '7 days') AS new_this_week,
  MIN(created_at)    AS first_joined,
  MAX(created_at)    AS last_joined
FROM users
GROUP BY role
HAVING COUNT(*) > 5
ORDER BY total DESC;

CTEs (Common Table Expressions)

-- Named subquery
WITH active_users AS (
  SELECT id, email
  FROM users
  WHERE last_login > NOW() - INTERVAL '30 days'
),
user_post_counts AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM posts
  WHERE published = TRUE
  GROUP BY user_id
)
SELECT u.email, COALESCE(p.cnt, 0) AS published_posts
FROM active_users u
LEFT JOIN user_post_counts p ON p.user_id = u.id
ORDER BY published_posts DESC;

-- Recursive CTE (e.g. org chart / tree)
WITH RECURSIVE org AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM employees
  WHERE parent_id IS NULL          -- root nodes

  UNION ALL

  SELECT e.id, e.name, e.parent_id, o.depth + 1
  FROM employees e
  JOIN org o ON e.parent_id = o.id
)
SELECT * FROM org ORDER BY depth, name;

Window functions

SELECT
  user_id,
  created_at,
  COUNT(*)    OVER (PARTITION BY user_id)                          AS user_total_posts,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)    AS post_rank,
  RANK()       OVER (ORDER BY created_at)                         AS global_rank,
  LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_post_date,
  SUM(view_count) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total_views
FROM posts;

JSONB

PostgreSQL's JSONB is binary JSON with indexing support — far more powerful than JSON.

-- Store JSON
INSERT INTO users (metadata)
VALUES ('{"plan": "pro", "features": ["api", "export"]}');

-- Extract field (returns JSONB)
SELECT metadata -> 'plan' FROM users;

-- Extract as text
SELECT metadata ->> 'plan' FROM users;

-- Nested field
SELECT metadata -> 'address' ->> 'city' FROM users;

-- Check key exists
SELECT * FROM users WHERE metadata ? 'plan';

-- Containment (requires GIN index)
SELECT * FROM users WHERE metadata @> '{"plan": "pro"}';

-- Any of multiple keys
SELECT * FROM users WHERE metadata ?| ARRAY['plan', 'tier'];

-- Update one field (immutable merge)
UPDATE users
SET metadata = metadata || '{"plan": "enterprise"}'::JSONB
WHERE id = 1;

-- Remove a key
UPDATE users
SET metadata = metadata - 'old_key'
WHERE id = 1;

-- Array element
SELECT metadata -> 'features' -> 0 FROM users;   -- first element
SELECT jsonb_array_length(metadata -> 'features') FROM users;

-- Expand JSON array into rows
SELECT u.id, feature
FROM users u,
     jsonb_array_elements_text(u.metadata -> 'features') AS feature;

-- Build JSON from columns
SELECT jsonb_build_object('id', id, 'email', email) FROM users;

-- Aggregate rows into JSON array
SELECT jsonb_agg(jsonb_build_object('id', id, 'email', email))
FROM users
WHERE role = 'admin';

Full-text search

-- Basic full-text search
SELECT title, body
FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql & cheat');

-- With ranking
SELECT
  title,
  ts_rank(to_tsvector('english', title || ' ' || body),
          to_tsquery('english', 'postgresql')) AS rank
FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql')
ORDER BY rank DESC;

-- Phrase search
WHERE tsvec @@ phraseto_tsquery('english', 'cheat sheet');

-- Prefix search (autocomplete)
WHERE tsvec @@ to_tsquery('english', 'postgre:*');

-- Stored tsvector column (for performance)
ALTER TABLE posts ADD COLUMN search_vec TSVECTOR;
UPDATE posts SET search_vec = to_tsvector('english', title || ' ' || COALESCE(body, ''));
CREATE INDEX idx_posts_search ON posts USING GIN(search_vec);

-- Auto-update with trigger
CREATE FUNCTION posts_search_update() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vec := to_tsvector('english', NEW.title || ' ' || COALESCE(NEW.body, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_trigger
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION posts_search_update();

INSERT, UPDATE, DELETE, UPSERT

-- Insert and return new row
INSERT INTO users (email, name, role)
VALUES ('alice@example.com', 'Alice', 'admin')
RETURNING *;

-- Bulk insert
INSERT INTO tags (name) VALUES ('postgres'), ('sql'), ('database');

-- Insert from SELECT
INSERT INTO archived_posts SELECT * FROM posts WHERE created_at < NOW() - INTERVAL '1 year';

-- Upsert (INSERT … ON CONFLICT)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
RETURNING *;

-- Upsert — ignore conflicts
INSERT INTO tags (name) VALUES ('sql')
ON CONFLICT (name) DO NOTHING;

-- Update with RETURNING
UPDATE users
SET role = 'admin', updated_at = NOW()
WHERE email = 'alice@example.com'
RETURNING id, email, role;

-- Update from another table
UPDATE posts p
SET view_count = p.view_count + s.delta
FROM view_deltas s
WHERE s.post_id = p.id;

-- Delete and return deleted rows
DELETE FROM users WHERE last_login < NOW() - INTERVAL '2 years'
RETURNING id, email;

-- Truncate (fast, no per-row triggers)
TRUNCATE posts RESTART IDENTITY CASCADE;

Administration

Database management

-- Create database
CREATE DATABASE myapp
  WITH OWNER = myuser
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8';

-- Drop database (can't drop current connection)
DROP DATABASE old_app;

-- Rename database
ALTER DATABASE old_name RENAME TO new_name;

-- Database size
SELECT pg_size_pretty(pg_database_size('myapp'));

-- All databases with sizes
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database ORDER BY pg_database_size(datname) DESC;

Users and permissions

-- Create role (user)
CREATE ROLE appuser WITH LOGIN PASSWORD 'secretpass';

-- Create read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Assign role
GRANT readonly TO appuser;

-- Grant specific privilege
GRANT INSERT, UPDATE, DELETE ON users TO appuser;

-- Revoke
REVOKE DELETE ON users FROM appuser;

-- View grants
\dp tablename

-- Change password
ALTER ROLE appuser WITH PASSWORD 'newpass';

-- Drop user
DROP ROLE appuser;

Table sizes and bloat

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

-- Index sizes
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- VACUUM to reclaim space (normally runs automatically)
VACUUM ANALYZE users;
VACUUM FULL users;   -- rewrites table — locks it, reclaims most space

Performance queries

-- Running queries
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query != '<IDLE>'
ORDER BY duration DESC;

-- Slow queries (requires pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Unused indexes (candidates for removal)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY tablename;

-- Missing indexes (high seq scans on large tables)
SELECT relname, seq_scan, idx_scan,
       seq_scan - idx_scan AS too_much_seq,
       pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND pg_total_relation_size(relid) > 100000
ORDER BY too_much_seq DESC;

-- Kill a query
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

EXPLAIN and query plans

-- Show execution plan (estimated)
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Show actual timing (runs the query)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- Verbose + buffers (for detailed I/O analysis)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM posts WHERE user_id = 1;

-- What to look for:
-- Seq Scan on large table → missing index
-- Hash Join / Nested Loop → check join conditions
-- rows=1000 vs Actual Rows=50000 → stale statistics → run ANALYZE
-- Buffers: hit=1000 read=0 → data cached (good)

Transactions and locking

-- Basic transaction
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 goes wrong:
ROLLBACK;

-- Savepoints (partial rollback)
BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT order_saved;
INSERT INTO payments (...) VALUES (...);
-- payment failed:
ROLLBACK TO SAVEPOINT order_saved;
-- continue with order only
COMMIT;

-- Advisory locks (application-level locks)
SELECT pg_try_advisory_lock(12345);          -- non-blocking
SELECT pg_advisory_lock(12345);              -- blocking
SELECT pg_advisory_unlock(12345);

-- Row-level locking
SELECT * FROM users WHERE id = 1 FOR UPDATE;          -- exclusive
SELECT * FROM users WHERE id = 1 FOR SHARE;           -- shared
SELECT * FROM users WHERE id = 1 FOR UPDATE SKIP LOCKED; -- skip locked rows (job queue pattern)

Common mistakes

6 FAQ

Q: PostgreSQL vs MySQL — which should I choose?
PostgreSQL wins on standards compliance, JSONB, full-text search, window functions, CTEs, and extensibility. MySQL/MariaDB is simpler to operate. For new projects, Postgres is typically the better choice.

Q: What's the difference between SERIAL and GENERATED ALWAYS AS IDENTITY?
SERIAL is a Postgres shorthand that creates a sequence; it's not SQL-standard and allows manual inserts that can break the sequence. GENERATED ALWAYS AS IDENTITY (Postgres 10+) is the SQL standard — prefer it for new tables: id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

Q: When should I use JSONB vs separate columns?
Use separate columns for fields you filter/sort/join on frequently — indexes and query plans work best with typed columns. Use JSONB for semi-structured, optional, or user-defined attributes (metadata, settings, feature flags).

Q: How do I handle migrations safely in production?

1. CREATE INDEX CONCURRENTLY — never block reads/writes for index creation. 2. ADD COLUMN with a nullable or default-less column — instant, no table rewrite. 3. ADD COLUMN … DEFAULT val on Postgres 11+ is instant (stored in catalog, not rewritten). 4. Always test with EXPLAIN ANALYZE before running on production data.

Q: How do I paginate efficiently for large tables?
Offset pagination (LIMIT n OFFSET n*page) degrades on large offsets — Postgres must scan and discard rows. Use keyset/cursor pagination instead: WHERE id > last_seen_id ORDER BY id LIMIT 20. It stays fast at any depth.

Q: What extensions should I know about?
pg_stat_statements — track slow queries. pg_trgm — trigram-based LIKE/ILIKE indexes. uuid-ossp or built-in gen_random_uuid() — UUID generation. pgcrypto — encryption functions. PostGIS — geospatial queries. pg_partman — table partitioning management.