SQL Cheat Sheet (PostgreSQL)

SELECT, JOINs, window functions, CTEs, indexes, JSON — with copy-paste examples

SELECT — Querying Data
DQL SELECT — retrieve rows
SELECT * FROM users;
SELECT name, email FROM users;
SELECT DISTINCT country FROM users;
💡 ; terminates statement. * returns all columns. List specific columns for better performance.
DQL WHERE — filter rows
SELECT * FROM users WHERE age >= 18;
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM logs WHERE created_at >= NOW() - INTERVAL '7 days';
💡 Operators: =, != or <>, <, >, IN, LIKE (% wildcard), IS NULL, IS NOT NULL.

Sorting & Limiting

ORDER BY — sort results
SELECT * FROM users ORDER BY created_at DESC;
ORDER BY name ASC, age DESC (multi-column)
LIMIT / OFFSET — paginate
SELECT * FROM posts LIMIT 10;
LIMIT 20 OFFSET 40 -- page 3, 20 per page
LIMIT 20 ROWS OFFSET 40 -- ANSI SQL
JOINs — Combine Tables
Join INNER JOIN — matching rows only
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
💡 INNER JOIN returns only rows with matches in both tables. JOIN keyword alone = INNER JOIN.
Join LEFT JOIN — all from left, match from right
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
💡 LEFT JOIN returns all rows from left table. Unmatched right columns are NULL. Use to find orphans: add WHERE o.id IS NULL.
Join FULL OUTER JOIN — all rows from both
SELECT COALESCE(u.name, 'Unknown'), o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
💡 FULL OUTER JOIN returns all rows from both tables. Unmatched sides are NULL. COALESCE() replaces NULL with fallback.
Join CROSS JOIN — Cartesian product
SELECT c.name, p.name
FROM colors c CROSS JOIN products p;
-- Equivalent to:
SELECT * FROM table1, table2;
⚠️ Produces m × n rows. Use sparingly — result set grows exponentially.

Other Join Types

SELF JOIN — join table to itself
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
NATURAL JOIN
— auto-join on same-named cols
SELECT * FROM table1 NATURAL JOIN table2;
⚠️ Implicit, brittle. Prefer explicit ON.
Aggregation & Grouping
Agg GROUP BY — aggregate groups
SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country;
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;
💡 WHERE filters rows before grouping. HAVING filters groups after aggregation. Non-aggregated columns must appear in GROUP BY.

Aggregate Functions

COUNT(*) — row count
COUNT(col) — non-null count
SUM(col) — total
AVG(col) — average
MIN(col) — minimum
MAX(col) — maximum
STRING_AGG(col, ', ') — PostgreSQL string concat
ARRAY_AGG(col) — collect into array
Window Functions (Advanced)
Window OVER() — windowed calculations
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
💡 Window functions compute across a set of rows related to current row, but don't collapse rows (unlike GROUP BY).
Window PARTITION BY — group within window
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
💡 PARTITION BY splits rows into partitions. Window functions restart for each partition.

Window Functions Reference

Ranking:
ROW_NUMBER() — unique sequential
RANK() — standard ranking (gaps)
DENSE_RANK() — no gaps
NTILE(4) — quartiles
Analytics:
LAG(col, 1) — previous row
LEAD(col, 1) — next row
FIRST_VALUE(col)
LAST_VALUE(col)
Aggregates as windows:
SUM() OVER ()
AVG() OVER ()
COUNT() OVER ()
MIN()/MAX() OVER ()
CTEs — WITH Queries
CTE WITH — Common Table Expression
WITH active_users AS (
SELECT * FROM users WHERE active = true
), recent_orders AS (
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT u.name, COUNT(o.id) AS order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.name;
💡 CTEs make complex queries readable. They're like temp views scoped to one statement. Can be recursive (for trees, graphs).
DML — Data Manipulation
DML INSERT — add rows
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name) VALUES ('Bob'), ('Charlie'), ('Dana'); -- multi-row
INSERT INTO users SELECT * FROM staging_users; -- from query
💡 Omit column list only if providing values for ALL columns in exact table order. SERIAL columns (auto-increment) can be omitted.
DML UPDATE — modify rows
UPDATE users SET active = true WHERE last_login > NOW() - INTERVAL '30 days';
UPDATE products SET price = price * 1.1; -- increase all by 10%
UPDATE users SET meta = jsonb_set(meta, '{theme}', '"dark"') WHERE id = 1;
⚠️ Always include a WHERE unless you truly mean to update every row. WHERE missing = full table update.
DML DELETE — remove rows
DELETE FROM users WHERE last_login < NOW() - INTERVAL '1 year';
DELETE FROM sessions WHERE user_id = 123;
⚠️ DELETE FROM table_name without WHERE removes every row (but keeps table structure). Use TRUNCATE for faster full-table clear.
DML TRUNCATE — fast delete all rows
TRUNCATE TABLE logs;
TRUNCATE TABLE users, orders RESTART IDENTITY CASCADE;
💡 TRUNCATE is DDL, not DML — it's fast (doesn't row-by-row delete), resets identity columns by default in PG. RESTART IDENTITY resets auto-increment. CASCADE truncates dependent tables.
DDL — Schema Objects
DDL CREATE TABLE — new table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
💡 SERIAL auto-increment integer in PG. VARCHAR(n) variable string with limit. TIMESTAMP with time zone = TIMESTAMPTZ. DEFAULT values set on insert.
DDL ALTER TABLE — modify existing table
ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN middle_name;
ALTER TABLE users RENAME TO app_users;
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
💡 ALTER commands are transactional in PG — you can ROLLBACK an ALTER TABLE.
Indexes — Performance

Create & Manage Indexes

Basic index
CREATE INDEX idx_email ON users(email);

UNIQUE index
CREATE UNIQUE INDEX idx_users_email ON users(email);
Composite index
CREATE INDEX idx_last_first ON users(last_name, first_name);

Partial index (filtered)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
Drop index
DROP INDEX idx_email;

List indexes
SELECT * FROM pg_indexes WHERE tablename = 'users';
💡 Indexes speed up queries but slow down writes. Use on WHERE, JOIN ON, and ORDER BY columns. Partial indexes are efficient for subset queries (e.g., only active users).
PostgreSQL Specific
Postgres JSONB — JSON with binary storage
-- Column type
CREATE TABLE users (
meta JSONB
);
-- Insert JSON
INSERT INTO users (meta) VALUES ('{"theme": "dark", "notifications": true}');
-- Query jsonb field
SELECT meta->>'theme' AS theme FROM users;
-- Check contains
SELECT * FROM users WHERE meta @> '{"theme": "dark"}';
-- Update field
UPDATE users SET meta = jsonb_set(meta, '{theme}', '"light"') WHERE id = 1;
💡 -> returns JSON object, ->> returns text. @> contains operator. jsonb_set() updates nested path. Use GIN indexes on JSONB for performance: CREATE INDEX idx_meta ON users USING GIN (meta);
Postgres ON CONFLICT — UPSERT
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;
-- Ignore duplicates
INSERT ... ON CONFLICT DO NOTHING;
💡 EXCLUDED is the row that would have been inserted. ON CONFLICT (column) needs a UNIQUE or PRIMARY KEY constraint on that column.
Postgres RETURNING — get modified rows
INSERT INTO users (name) VALUES ('Bob') RETURNING id;
UPDATE users SET active = false WHERE last_login < '2020-01-01' RETURNING id, email;
DELETE FROM sessions WHERE expired = true RETURNING COUNT(*);
💡 RETURNING works with INSERT, UPDATE, DELETE, and even MERGE. Saves a second query. Returns actual values (including defaults, triggers).

Recursive CTE — Hierarchical Data

WITH RECURSIVE subtree AS (
SELECT id, parent_id, name FROM categories WHERE id = 5
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM categories c
INNER JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;
💡 Recursive CTEs traverse trees (org charts, categories). Anchor query + recursive UNION ALL. Use WHERE NOT EXISTS or max depth to prevent infinite loops.
PostgreSQL Index Types

Index Types Quick Ref

BTREE (default)
CREATE INDEX ... — normal
Great for range queries, equality
HASH
CREATE INDEX ... ON table USING HASH (col);
Only equality, very fast, no ordering
GIN (Generalized Inverted)
CREATE INDEX ... ON table USING GIN (jsonb_col);
JSONB, arrays, full-text tsvector
GiST / SP-GiST
CREATE INDEX ... ON table USING GiST (point_col);
Geospatial, range types, full-text
BRIN (Block Range)
CREATE INDEX ... ON table USING BRIN (created_at);
Large sorted tables, time-series data. Small, fast.

People Also Use These Tools

SQL Formatter

Beautify SQL queries

JSON Formatter

Format and validate JSON

Regex Tester

Test regular expressions

UUID Generator

Generate UUIDs

Base64 Encoder

Encode/decode Base64

Crontab Descriptor

Decode cron schedules

About This Tool

The SQL Cheat Sheet (PostgreSQL) covers the most essential SQL commands with practical examples. From basic SELECT queries to advanced window functions, CTEs, and PostgreSQL-specific features like JSONB and UPSERT, this reference has you covered. Each command includes syntax, a copy-ready example, and tips for effective usage.

SQL Quick Reference

📤 DQL — Data Query

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT .... FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT (execution order differs from written order).

✏️ DML — Data Manipulation

INSERT adds rows. UPDATE modifies. DELETE removes. TRUNCATE fast full-table delete (DDL variant). Use transactions: BEGIN; ... COMMIT; or ROLLBACK;.

🏗️ DDL — Data Definition

CREATE tables, indexes, types. ALTER modify. DROP remove. TRUNCATE is also DDL (fast, commits immediately in some DBs).

🚀 PostgreSQL Power Features

Window functions (running totals, ranks), CTEs (recursive trees), JSONB (document store), ON CONFLICT (UPSERT), RETURNING (output modified rows). Plus partial/fulltext/BRIN indexes.

Frequently Asked Questions

JOIN order: does sequence matter?

Yes. INNER JOIN order doesn't affect results (mathematically commutative) but can affect query plan and performance. LEFT JOIN order matters — the "preserved" table is the left side of each LEFT JOIN. Put the table you want all rows from on the left.

What's the difference BETWEEN WHERE and HAVING?

WHERE filters rows before grouping/aggregation. HAVING filters groups after GROUP BY. You cannot use aggregate functions in WHERE. Use HAVING COUNT(*) > 5 to filter groups with 5+ rows.

When should I use a CTE vs subquery?

CTEs improve readability for complex, multi-step queries and enable recursion. However, in PostgreSQL (pre-12), CTEs were optimization fences (materialized). For performance-critical queries with large datasets, consider subqueries or WITH ... NOT MATERIALIZED (PG 12+).

Why is my query slow? Index strategy?

Slow SELECT? Check missing indexes on WHERE, JOIN ON, and ORDER BY columns. Use EXPLAIN ANALYZE to see query plan. For JSONB searches, add GIN index: CREATE INDEX ... ON table USING GIN (jsonb_col);. For time-series, BRIN indexes on sorted datetime columns are tiny and fast.

Is this tool free?

Yes, completely free with no sign-up required.

Related Tools