← All Cheat Codes Library / Cheat Codes / PostgreSQL

🐘 PostgreSQL Cheat Codes

PostgreSQL Cheat Codes

PostgreSQL Cheat Codes for queries, JSONB, window functions, and WAL storage administration. 20 essential cheat codes ready to copy and execute.

🌐 All Engines 🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

# 1 SELECT DQL

Retrieve columns from table.

Syntax:

SELECT col1, col2 FROM tbl;

Working Example:

SELECT id, email FROM users;

# 2 WHERE DQL

Filter query output rows.

Syntax:

SELECT * FROM tbl WHERE col = val;

Working Example:

SELECT * FROM orders WHERE total > 100;

# 3 JOIN Joins

Combine rows from two tables on key.

Syntax:

SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;

Working Example:

SELECT u.name, o.id FROM users u JOIN orders o ON u.id = o.user_id;

# 4 GROUP BY Aggregations

Group rows for aggregate computations.

Syntax:

SELECT col, COUNT(*) FROM tbl GROUP BY col;

Working Example:

SELECT dept, COUNT(*) FROM emp GROUP BY dept;

# 5 HAVING Aggregations

Filter aggregated grouped output.

Syntax:

SELECT col, AVG(x) FROM tbl GROUP BY col HAVING AVG(x) > 50;

Working Example:

SELECT dept, AVG(sal) FROM emp GROUP BY dept HAVING AVG(sal) > 50000;

# 6 INSERT INTO DML

Add new row records into table.

Syntax:

INSERT INTO tbl (col1) VALUES (val1);

Working Example:

INSERT INTO users (email) VALUES ('a@b.com');

# 7 UPDATE SET DML

Modify existing table column values.

Syntax:

UPDATE tbl SET col = val WHERE condition;

Working Example:

UPDATE users SET status = 'active' WHERE id = 1;

# 8 DELETE FROM DML

Remove records matching condition.

Syntax:

DELETE FROM tbl WHERE condition;

Working Example:

DELETE FROM sessions WHERE expired = true;

# 9 ON CONFLICT (UPSERT) DML

Insert row or update on key conflict.

Syntax:

INSERT INTO tbl (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;

Working Example:

INSERT INTO stats (id, count) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET count = stats.count + 1;

# 10 RETURNING DML

Return modified column values directly.

Syntax:

INSERT INTO tbl (col) VALUES (val) RETURNING id;

Working Example:

INSERT INTO users (email) VALUES ('a@b.com') RETURNING id, created_at;

# 11 JSONB Query (->>) JSONB

Extract JSON field as text string.

Syntax:

SELECT data->>'field' FROM tbl;

Working Example:

SELECT metadata->>'author' FROM docs;

# 12 JSONB Contains (@>) JSONB

Check if JSONB contains key-value pair.

Syntax:

SELECT * FROM tbl WHERE data @> '{"key": "val"}';

Working Example:

SELECT * FROM events WHERE payload @> '{"type": "click"}';

# 13 STRING_AGG Aggregations

Concatenate group strings with delimiter.

Syntax:

SELECT STRING_AGG(col, ', ') FROM tbl GROUP BY grp;

Working Example:

SELECT dept, STRING_AGG(name, ', ') FROM emp GROUP BY dept;

# 14 EXPLAIN ANALYZE Performance

Execute query and return real runtime stats.

Syntax:

EXPLAIN ANALYZE SELECT * FROM tbl WHERE col = val;

Working Example:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

# 15 CREATE INDEX DDL

Build B-Tree index to accelerate lookups.

Syntax:

CREATE INDEX idx_name ON tbl(col);

Working Example:

CREATE INDEX idx_users_email ON users(email);

# 16 CREATE GIN INDEX DDL

Build GIN index for JSONB or array columns.

Syntax:

CREATE INDEX idx_name ON tbl USING GIN (json_col);

Working Example:

CREATE INDEX idx_docs_meta ON docs USING GIN (metadata);

# 17 WITH RECURSIVE (CTE) Advanced

Query hierarchical org or tree data.

Syntax:

WITH RECURSIVE cte AS (...) SELECT * FROM cte;

Working Example:

WITH RECURSIVE tree AS (SELECT id FROM emp UNION ALL SELECT e.id FROM emp e JOIN tree t ON e.mgr_id = t.id) SELECT * FROM tree;

# 18 ROW_NUMBER() OVER Window

Assign row index numbers within partition.

Syntax:

SELECT col, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val) FROM tbl;

Working Example:

SELECT name, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY sal DESC) FROM emp;

# 19 VACUUM ANALYZE Maintenance

Reclaim dead tuples and update statistics.

Syntax:

VACUUM ANALYZE tbl_name;

Working Example:

VACUUM ANALYZE users;

# 20 TRUNCATE TABLE DDL

Rapidly wipe all rows from table.

Syntax:

TRUNCATE TABLE tbl RESTART IDENTITY;

Working Example:

TRUNCATE TABLE staging_events RESTART IDENTITY;