PostgreSQL Cheat Sheet

Connect & Launch

Meta-commands (backslash) 🔥

COPY — Import & Export

Type Reference 🔥

Type Casting

Arrays

CREATE TABLE 🔥

ALTER TABLE

DROP & Schemas

Custom Types & Enums

INSERT

UPSERT — ON CONFLICT ⭐

UPDATE & DELETE

SELECT Clause Order 🔥

WHERE Operators

DISTINCT, LIMIT & FETCH

Useful Scalar Functions

Date & Time Functions

Join Types Visual 🔥

JOIN Syntax

SELF JOIN & LATERAL

USING & NATURAL

Aggregate Functions 🔥

GROUP BY Patterns

Useful Aggregation Patterns

Window Function Syntax 🔥

Ranking Functions

Value & Navigation Functions

Running Totals & Moving Avg ⭐

CTEs (Common Table Expressions) 🔥

Subqueries

Set Operations

Constraint Types 🔥

Foreign Key Actions

Partial & Expression Constraints

Index Types 🔥

CREATE INDEX Syntax

Index Management

Covering Index (INCLUDE) ⭐

ACID Properties

Transaction Commands

Isolation Levels

Locking

Regular Views

Materialized Views ⭐

PL/pgSQL Function 🔥

Stored Procedure (no return)

Function Volatility & Language

Trigger Pattern 🔥

JSONB Operators 🔥

Query JSONB

Modify & Build JSONB

Basic FTS

FTS with Generated Column + GIN Index 🔥

EXPLAIN & Node Types 🔥

Key Performance Queries

VACUUM & ANALYZE

Tuning Tips ⭐

shared_buffers — set to 25% of RAM (e.g. <code>2GB</code> on 8GB server)
work_mem — memory per sort/hash; set 16–64MB; watch for many connections
effective_cache_size — estimate of OS cache; set 75% of RAM
enable_seqscan=off — force index use in session for testing
Use <code>EXPLAIN (ANALYZE, BUFFERS)</code> to find buffer hits vs disk reads
Always <code>ANALYZE</code> after bulk load — stale stats = bad plans
Use <code>CREATE INDEX CONCURRENTLY</code> in production — no table lock
Use <code>CLUSTER table_name USING index_name</code> to physically reorder rows
Connection pooling with PgBouncer — PostgreSQL handles many connections poorly

Roles & Users

GRANT & REVOKE

Row-Level Security (RLS) ⭐

Master SQL Cheat Table 🔥

PostgreSQL Gotchas ⭐

NULL comparisons — <code>NULL = NULL</code> is <code>NULL</code>, not true. Use <code>IS NULL</code> or <code>IS DISTINCT FROM</code>
SERIAL vs IDENTITY — prefer <code>GENERATED ALWAYS AS IDENTITY</code> (SQL standard, more control)
TIMESTAMPTZ vs TIMESTAMP — always use <code>TIMESTAMPTZ</code>; it stores UTC and converts per session timezone
JSON vs JSONB — always use JSONB; it's indexed, faster to query, removes key duplicates
LIKE with index — only <code>LIKE 'prefix%'</code> uses B-tree. For <code>%suffix%</code> use <code>pg_trgm</code> GIN index
UNION vs UNION ALL — UNION deduplicates (slow!). Use UNION ALL unless you need dedup
Arrays are 1-indexed — <code>arr[1]</code> is first element, not <code>arr[0]</code>
WHERE vs HAVING — WHERE filters rows before grouping; HAVING filters groups after
UPDATE with JOIN — use <code>UPDATE a ... FROM b WHERE a.id=b.id</code> (not JOIN syntax)
VACUUM bloat — PostgreSQL doesn't reuse dead rows across transactions until VACUUM runs
SELECT * in production — avoid in views/apps; schema changes silently break queries
LIMIT without ORDER BY — returns arbitrary rows; always add ORDER BY for deterministic paging

Common Extensions