PostgreSQL Extensions — IDRM Top 20 Cheat Sheet

01Connect & psql Flagsthe command line

psql -U user -d db
Connect as a specific user to a database.
psql -h host -p 5432 -U user -d db★
Connect to a remote server.
psql -c "SQL"
Run one command, then exit.
psql db -f file.sql
Execute commands from a script file.
sudo -u postgres psql★
Local shortcut: become the postgres superuser.
\c dbname★
Switch databases inside an open session.
\conninfo
Show the current connection's details.
\q★
Quit psql.

02psql Meta-Commandsthe backslash family

\l★
List all databases.
\dt★
List tables in the current schema.
\d table★
Describe a table: columns, indexes, FKs.
\d+ table
…plus disk size and storage details.
\dn
List schemas.
\di
List indexes.
\dv
List views.
\df
List functions.
\du★
List roles and their attributes.
\x★
Toggle expanded (vertical) row output.
\timing
Toggle display of each query's run time.
\! cmd
Run a shell command without leaving psql.
\i file
Execute SQL from a file.

03Databases & Schemastop-level containers

CREATE DATABASE name;★
Create a new database.
CREATE DATABASE name OWNER user;
…owned by a specific role.
ALTER DATABASE old RENAME TO new;
Rename a database.
DROP DATABASE name;destroys
Delete a database permanently.
CREATE SCHEMA IF NOT EXISTS name;
A namespace to group related tables.
DROP SCHEMA name CASCADE;destroys
Delete a schema and everything in it.

04Create & Alter Tablesstructure

CREATE TABLE t (col type, …);★
Define a new table.
id SERIAL PRIMARY KEY★
Auto-incrementing key (legacy syntax).
id INT GENERATED ALWAYS AS IDENTITY
Modern, SQL-standard auto-increment.
FOREIGN KEY (col) REFERENCES other(id)
Link this table to another.
ALTER TABLE t ADD COLUMN c type;★
Add a column.
ALTER TABLE t RENAME COLUMN a TO b;
Rename a column.
ALTER TABLE t ALTER COLUMN c TYPE type;
Change a column's data type.
ALTER TABLE t DROP COLUMN c;
Remove a column.
ALTER TABLE t RENAME TO new;
Rename the table itself.
DROP TABLE IF EXISTS t CASCADE;destroys
Delete a table + dependent objects.
TRUNCATE TABLE t;destroys
Wipe all rows instantly, keep structure.

05Constraintsdata integrity rules

PRIMARY KEY★
Unique + NOT NULL row identifier.
UNIQUE
No duplicate values (NULLs exempt).
NOT NULL
Column can never be empty.
CHECK (expr)
Row must satisfy a boolean condition.
FOREIGN KEY … REFERENCES …★
Enforce a link to another table's key.
ADD CONSTRAINT name …
Name and attach a constraint after creation.

06Views, Sequences & TypesDDL — derived & custom objects

CREATE VIEW v AS SELECT …;★
A saved query you can select from like a table.
CREATE MATERIALIZED VIEW mv AS SELECT …;★
Stores the results on disk — fast reads, stale until refreshed.
REFRESH MATERIALIZED VIEW [CONCURRENTLY] mv;
Recompute a matview; CONCURRENTLY avoids locking readers.
CREATE TYPE mood AS ENUM ('low','ok','high');★
A custom enumerated type with a fixed value set.
CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '@');
A reusable type = base type + constraint.
CREATE SEQUENCE s START 1 INCREMENT 1;
A standalone counter; nextval('s') to draw from it.
c type GENERATED ALWAYS AS (expr) STORED
A computed column, persisted on write.
COMMENT ON TABLE t IS 'note';
Attach documentation to any object.

07Table PartitioningDDL — split big tables

CREATE TABLE t (…) PARTITION BY RANGE (col);★
Declare a partitioned parent (RANGE / LIST / HASH).
CREATE TABLE t_2025 PARTITION OF t FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');★
Attach a range child partition.
… PARTITION OF t FOR VALUES IN ('AP','TN');
A LIST partition for discrete values.
… PARTITION OF t FOR VALUES WITH (MODULUS 4, REMAINDER 0);
A HASH partition, spreading rows evenly.
ALTER TABLE t DETACH PARTITION t_2025;
Remove a partition without dropping its data.

08Core Data Typeswhat to store columns as

INTEGER / BIGINT / NUMERIC(p,s)★
Whole numbers & exact decimals.
VARCHAR(n) / TEXT★
Bounded vs. unbounded text.
BOOLEAN
true / false.
DATE / TIME / TIMESTAMPTZ★
Calendar date, time, timestamp+zone.
JSON / JSONB★
Document data — JSONB is binary & indexable.
UUID
Universally unique identifier.
integer[]
Array of any base type.
inet / cidr / macaddr
Network address types.

09Query BasicsSELECT, sort, page

SELECT col1, col2 FROM t;★
Fetch specific columns.
SELECT * FROM t;
Fetch every column.
SELECT DISTINCT col FROM t;
Unique values only.
… ORDER BY col [ASC|DESC];★
Sort the result set.
… LIMIT n OFFSET m;★
Page through results.
col AS alias
Rename a column in the output.

10FilteringWHERE clauses

WHERE col = value★
Exact match.
WHERE col BETWEEN a AND b
Inclusive numeric/date range.
WHERE col IN (a, b, c)
Matches any of several values.
WHERE col LIKE '%abc%'★
Pattern match, case-sensitive.
WHERE col ILIKE '%abc%'
Pattern match, case-insensitive.
WHERE col IS [NOT] NULL★
Missing-value checks — never use = NULL.
WHERE condn1 [AND|OR] condn2
Combine multiple conditions.

11Joinscombine tables — see diagrams ↓

JOIN b ON a.id = b.a_id★
INNER join (the default) — matches only.
LEFT JOIN b ON …★
All of A, plus matches from B.
RIGHT JOIN b ON …
All of B, plus matches from A.
FULL JOIN b ON …
Everything from both sides.
CROSS JOIN b
Every row of A × every row of B.
a JOIN b USING (col)
Shorthand when join-column names match.

12CTEs & SubqueriesDQL — compose queries

WITH cte AS (SELECT …) SELECT * FROM cte;★
Name a subquery up front for readability & reuse.
WITH RECURSIVE r AS (… UNION ALL …)★
Walk trees / graphs / hierarchies (org charts, BOMs).
WHERE EXISTS (SELECT 1 FROM b WHERE …)★
Row-exists test — usually faster than IN for big sets.
WHERE col = (SELECT MAX(col) FROM t)
Scalar subquery — returns exactly one value.
WHERE col = ANY(…) / ALL(…)
Compare against a set (ANY = some, ALL = every).
JOIN LATERAL (SELECT … WHERE x=t.id) s ON true
A subquery that can reference the outer row (top-N per group).

13Set Ops, CASE & Grouping SetsDQL — combine & branch

q1 UNION q2 / UNION ALL q2★
Stack rows; ALL keeps duplicates (and is faster).
q1 INTERSECT q2 / EXCEPT q2
Rows in both / rows in the first but not the second.
CASE WHEN c THEN a ELSE b END★
Inline if/else — usable in SELECT, WHERE, ORDER BY.
COUNT(*) FILTER (WHERE condn)★
Conditional aggregate — cleaner than SUM(CASE…).
GROUP BY ROLLUP (a, b) / CUBE (a, b)
Subtotals & grand totals across group combinations.
GROUP BY GROUPING SETS ((a),(b),())
Several groupings in one pass.

14Aggregation & Groupingsummarize rows

COUNT(*) / COUNT(col) / COUNT(DISTINCT col)★
Row counts, three flavors.
SUM / AVG / MIN / MAX(col)★
Column summaries.
GROUP BY col★
Roll rows up by shared value.
GROUP BY col HAVING condn★
Filter groups, not raw rows.
STRING_AGG(col, ', ') / ARRAY_AGG(col)
Fold a group's values into one string / array.

15Window Functionsper-row aggregates, keep every row

fn(…) OVER (PARTITION BY c1 ORDER BY c2)★
The core shape — unlike GROUP BY, rows aren't collapsed.
PARTITION BY col★
Resets the window per group (optional — omit for the whole table).
SUM(x) OVER (PARTITION BY c1 ORDER BY c2)★
Running/partitioned total — a per-row aggregate.
ROW_NUMBER() OVER (… ORDER BY col)★
Sequential rank, 1,2,3… — never ties.
RANK() OVER (… ORDER BY col)
Rank with gaps after ties (1,2,2,4).
DENSE_RANK() OVER (… ORDER BY col)
Rank with no gaps after ties (1,2,2,3).
LAG(col, n) / LEAD(col, n) OVER (…)★
Previous / next row's value — great for deltas.
FIRST_VALUE(col) / LAST_VALUE(col) OVER (…)
Value at the start / end of the current frame.
NTILE(4) OVER (ORDER BY col)
Split rows into n roughly-equal buckets (quartiles, etc).
… ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Explicit frame boundary — default for a running total.
WINDOW w AS (…) … OVER w
Name a window once, reuse it across several functions.

16Modifying Datawrite operations

INSERT INTO t (c1,c2) VALUES (v1,v2), (v3,v4);★
Add one or many rows in a single statement.
… ON CONFLICT (id) DO UPDATE SET …;★
Upsert: insert, or update on key clash.
… ON CONFLICT DO NOTHING;
Silently skip rows that would violate a constraint.
INSERT/UPDATE/DELETE … RETURNING *;★
Get the affected rows back — e.g. a generated id.
UPDATE t SET c=v WHERE condn;★
Modify matching rows.
UPDATE t SET c=b.v FROM b WHERE t.id=b.id;
Update using values joined from another table.
DELETE FROM t USING b WHERE t.id=b.id;
Delete based on a join to another table.
MERGE INTO t USING src ON … WHEN MATCHED …;
SQL-standard conditional insert/update/delete (PG 15+).
DELETE FROM t;care
Removes every row (logged, row-by-row).
COPY t FROM 'file' CSV HEADER;
Bulk-load rows from a CSV file.

17Transactionsall-or-nothing changes

BEGIN;★
Start a transaction block.
COMMIT;★
Make changes permanent.
ROLLBACK;★
Undo everything since BEGIN.
SAVEPOINT name;
Set a mid-transaction checkpoint.
ROLLBACK TO SAVEPOINT name;
Undo back to that checkpoint only.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;★
Strictest level; others: READ COMMITTED (default), REPEATABLE READ.
SELECT … FOR UPDATE [SKIP LOCKED];★
Lock selected rows — the basis of a safe job queue.

18Indexes & Performancemake reads fast

CREATE INDEX idx ON t (col);★
Speed up lookups/sorts on a column.
CREATE UNIQUE INDEX idx ON t (LOWER(email));
Index + enforce uniqueness on an expression.
CREATE INDEX idx ON t (col) WHERE condn;
Partial index over a subset of rows.
DROP INDEX IF EXISTS idx;care
Remove an index (reads get slower).
EXPLAIN SELECT …;★
Show the planner's chosen query path.
EXPLAIN ANALYZE SELECT …;★
Plan + actually run it, with real timings.
ANALYZE t;
Refresh planner statistics for a table.

19Roles & Privilegeswho can do what

CREATE ROLE r WITH LOGIN PASSWORD 'x';
Create a login-capable role.
CREATE USER u WITH PASSWORD 'x';★
Shortcut for a login role.
ALTER ROLE r WITH PASSWORD 'new';
Change a role's password.
GRANT SELECT, INSERT ON t TO r;★
Allow specific actions on a table.
GRANT ALL PRIVILEGES ON DATABASE d TO r;
Grant full access to a database.
REVOKE INSERT ON t FROM r;
Remove a previously granted privilege.
ALTER DEFAULT PRIVILEGES … GRANT SELECT ON TABLES TO r;
Auto-grant on objects created later.
ALTER TABLE t ENABLE ROW LEVEL SECURITY;★
Turn on per-row access rules.
CREATE POLICY p ON t USING (owner = current_user);
Define who may see / change which rows.
DROP ROLE IF EXISTS r;care
Delete a role permanently.

20Text, Date & Castingcommon functions

a || b or CONCAT(a,b)★
Concatenate strings (|| propagates NULL; CONCAT skips it).
LENGTH(s) / UPPER(s) / LOWER(s)
Size and case conversion.
SUBSTRING(s FROM i FOR n)
Extract part of a string.
REPLACE(s, 'old', 'new')
Swap a substring.
CURRENT_DATE / NOW() / CURRENT_TIMESTAMP★
Current date/time.
EXTRACT(MONTH FROM date)
Pull out one component of a date.
DATE_TRUNC('month', ts)★
Round a timestamp down to a unit.
value::type or CAST(value AS type)★
Convert between types.
COALESCE(a, b, default)★
First non-NULL value in the list.
NULLIF(a, b)
Returns NULL if equal — guards against ÷0.

21Backup, Restore & CSVmove data in & out

pg_dump -U u -d db -f db.sql★
Plain-SQL logical backup.
pg_dump -Fc -U u -d db -f db.dump
Custom-format backup, for use with pg_restore.
pg_dumpall > all.sql
All databases plus roles & tablespaces.
psql -U u -d db -f db.sql★
Restore a plain-SQL backup.
pg_restore -d db db.dump -c
Restore a custom-format backup.
\copy t TO 'file.csv' CSV HEADER★
Export a table (or query) to CSV.
\copy t FROM 'file.csv' CSV HEADER★
Import a CSV file into a table.

22Monitoring & Maintenancekeep it healthy

SELECT * FROM pg_stat_activity;★
See active sessions & running queries.
SELECT pg_cancel_backend(pid);
Politely stop a running query.
SELECT pg_terminate_backend(pid);force
Force-kill a connection.
pg_size_pretty(pg_total_relation_size('t'))★
Human-readable table size on disk.
pg_size_pretty(pg_database_size('db'))
Total database size.
VACUUM [ANALYZE] t;★
Reclaim dead-row space + refresh stats.
SELECT version();
Show the server version.

23JSON & JSONBmodule — document data

data -> 'key' / data ->> 'key'★
Get a field as JSON (->) or as text (->>).
data #> '{a,b}' / data #>> '{a,b}'
Reach into a nested path (JSON / text).
data @> '{"k":1}'★
Containment — does the doc contain this? (GIN-indexable).
data ? 'key' / data ?| array['a','b']
Does a top-level key exist / any of these keys.
data @? '$.items[*] ? (@.qty > 5)'
JSONPath match (PG 12+).
jsonb_build_object('k', v) / jsonb_build_array(…)★
Assemble JSONB from columns.
jsonb_set(data, '{a,b}', '10'::jsonb)
Return the doc with one path updated.
jsonb_agg(row) / jsonb_object_agg(k, v)
Aggregate rows into one JSONB array / object.
jsonb_array_elements(data) / jsonb_each(data)★
Expand an array / object into rows (set-returning).
CREATE INDEX ON t USING GIN (data jsonb_path_ops);★
Index JSONB for fast @> / @? containment queries.

24Arraysmodule — multi-value columns

'{1,2,3}'::int[] / ARRAY[1,2,3]★
Two ways to write an array literal.
arr[1] / arr[2:3]
Index (1-based!) or slice an array.
arr @> ARRAY[2] / arr && ARRAY[2,9]★
Contains / overlaps — both GIN-indexable.
val = ANY(arr)★
Membership test — the array equivalent of IN.
array_length(arr, 1) / cardinality(arr)
Element count.
SELECT unnest(arr)★
Expand an array into one row per element.
array_agg(col) / array_to_string(arr, ',')
Rows → array; array → delimited text.

25Full-Text Searchmodule — search documents

to_tsvector('english', body)★
Normalize text into searchable lexemes.
to_tsquery('english', 'flood & relief')★
Build a query with & (and), | (or), ! (not).
plainto_tsquery(…) / websearch_to_tsquery(…)
Parse plain / Google-style user input safely.
tsvector @@ tsquery★
The match operator — true if the doc matches.
ts_rank(tsv, tsq) / ts_headline(…)
Relevance score / highlighted snippet.
CREATE INDEX ON t USING GIN (to_tsvector('english', body));★
GIN index — makes @@ searches fast.

26Functions, PL/pgSQL & Triggersmodule — server-side logic

CREATE FUNCTION f(a int) RETURNS int LANGUAGE sql AS $$ … $$;★
A plain SQL function.
LANGUAGE plpgsql AS $$ BEGIN … RETURN x; END $$;★
Procedural body: variables, IF, LOOP, exceptions.
CREATE PROCEDURE p() … — CALL p();
A procedure can COMMIT/ROLLBACK; call it with CALL.
DO $$ BEGIN … END $$;
Run an anonymous procedural block once.
CREATE TRIGGER trg BEFORE INSERT ON t FOR EACH ROW EXECUTE FUNCTION fn();★
Fire a function on insert/update/delete.
NEW.col / OLD.col / RETURN NEW;
Inside a row trigger: the incoming / previous row.

27Handy Extensionsmodule — CREATE EXTENSION

CREATE EXTENSION IF NOT EXISTS name;★
The generic form; list installed with \dx.
pg_trgm★
Trigram similarity — fuzzy search & fast ILIKE '%x%'.
uuid-ossp / pgcrypto★
gen_random_uuid(), hashing & encryption helpers.
hstore
Simple key/value pairs in one column.
postgres_fdw / dblink
Query tables in another PostgreSQL server.
pg_stat_statements★
Track slowest / most frequent queries for tuning.

#	Extension	Primary capability	IDRM	Kind
1	pg_stat_statements	Query performance monitoring	★★★★★	contrib
2	pgcrypto	Cryptography & secure values	★★★★★	contrib
3	pg_trgm	Fuzzy text search (trigram)	★★★★★	contrib
4	pgvector	Vector / semantic search	★★★★★	external
5	pgAudit	Detailed database auditing	★★★★★	external*
6	citext	Case-insensitive text	★★★★★	contrib
7	postgres_fdw	Remote PostgreSQL access	★★★★★	contrib
8	pg_cron	Scheduled SQL jobs	★★★★★	external
9	Native UUID / uuid-ossp	UUID generation	★★★★★	built-in / contrib
10	unaccent	Accent-insensitive search	★★★★	contrib
11	PostGIS	Geospatial data	★★★★	external
12	pg_repack	Online maintenance / de-bloating	★★★★	external
13	pg_partman	Partition management	★★★★	external
14	TimescaleDB	Time-series workloads	★★★★	external*
15	pgTAP	Database unit testing	★★★★	external
16	HypoPG	Hypothetical indexes	★★★★	external
17	pg_ivm	Incremental materialized views	★★★★	external
18	hll	Approximate distinct counts (HyperLogLog)	★★★	external
19	hstore	Lightweight key-value data	★★★	contrib
20	file_fdw	External files as tables	★★★	contrib

101Extensions 101install, list, update, drop

CREATE EXTENSION IF NOT EXISTS name;★
Install into the current database. Add WITH SCHEMA s, VERSION '1.2', or CASCADE (auto-install dependencies).
\dx \dx+ name★
List installed extensions / show the objects one owns. SELECT * FROM pg_available_extensions; shows what can be installed.
ALTER EXTENSION name UPDATE TO '1.3';
Upgrade an installed extension's SQL objects to a newer version.
DROP EXTENSION name CASCADE;destructive
CASCADE also drops every object that depends on it — columns, indexes, views. Check first.
shared_preload_libraries = 'a,b,c'needs restart
Some extensions load a background worker / hooks at startup — set this in postgresql.conf then restart before CREATE EXTENSION: pg_stat_statements, pg_cron, pgaudit, pg_partman_bgw, pg_ivm, timescaledb.
contrib vs external
contrib ships with PostgreSQL (install the postgresql-contrib OS package). external needs its own build/package, and on managed clouds must be on the provider's allow-list.

1pg_stat_statementscontribpreload★★★★★

shared_preload_libraries='pg_stat_statements'restart
Then CREATE EXTENSION pg_stat_statements;. Set compute_query_id = on so identical queries aggregate.
SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;★
The classic "find my slowest queries" view. Queries are normalized (constants replaced by $1).
shared_blks_hit, shared_blks_read, wal_bytes, temp_blks_written
Per-statement I/O and WAL accounting — spot cache misses, temp-file spills and write amplification.
pg_stat_statements.max = 5000 .track = top|all|none
max caps tracked statements (default 5000); track=all also records statements nested inside functions/procedures.
SELECT pg_stat_statements_reset(0,0,0);
Reset stats. Full signature (userid, dbid, queryid, minmax_only) — pass a queryid to reset one statement, minmax_only=>true to clear only min/max timings.

2pgcryptocontrib★★★★★

CREATE EXTENSION pgcrypto;★
Hashing, HMAC, random bytes, password crypt, and PGP symmetric/public-key encryption.
digest('data','sha256') hmac('data', key, 'sha256')
One-way hash and keyed HMAC; both return bytea (wrap in encode(…,'hex') for text).
crypt(pw, gen_salt('bf', 10))★
Salted password hash (Blowfish). Verify with WHERE pw_hash = crypt(input, pw_hash).
pgp_sym_encrypt(data, psw) / pgp_sym_decrypt(msg, psw)
Symmetric PGP encryption of column values; pgp_pub_encrypt/decrypt for key-pair mode.
gen_random_bytes(16)
Cryptographically strong random bytes. Note: gen_random_uuid() is now core (PG13+) — no longer needs pgcrypto.
pgcrypto.builtin_crypto_enabled fips_mode()PG18 / FIPS
PG18 adds a FIPS switch; legacy ciphers (bf, 3des, cast5, twofish) are deprecated. Prefer AES-based / SHA-256 algorithms.

3pg_trgmcontrib★★★★★

CREATE EXTENSION pg_trgm;★
Trigram similarity for fuzzy matching, typo tolerance, and fast substring search.
SELECT name WHERE name % 'jonh';★
% = similarity above pg_trgm.similarity_threshold (default 0.3). similarity(a,b) returns the 0–1 score.
CREATE INDEX ON t USING gin (name gin_trgm_ops);★
Makes ILIKE '%x%', ~* regex and % index-accelerated — the #1 reason people install pg_trgm.
ORDER BY name <-> 'jonh' LIMIT 5;
<-> = distance (1−similarity) for nearest-match ranking; use a gist_trgm_ops GiST index for KNN ordering.
word_similarity() <% strict_word_similarity() <<%
Match a short term inside a longer string (own thresholds: word_similarity_threshold 0.6, strict 0.5).

4pgvectorexternal★★★★★ · 0.8

CREATE EXTENSION vector;★
Extension is named vector. Types: vector(n), halfvec(n) (2× smaller), sparsevec, bit.
CREATE TABLE items (id bigserial, embedding vector(1536));
Store embeddings as a fixed-dimension column alongside your normal data.
SELECT id ORDER BY embedding <=> '[…]' LIMIT 5;★
Operators: <-> L2, <=> cosine, <#> negative inner-product, <+> L1. Bit types add Hamming/Jaccard.
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);★
HNSW (default choice) or ivfflat. Pick the op-class to match your distance: vector_l2_ops, vector_ip_ops, etc.
SET hnsw.iterative_scan = 'strict_order';0.8+
Iterative index scans fix under-returning when a WHERE filters out most neighbours. relaxed_order trades exactness for recall; bounded by hnsw.max_scan_tuples.

5pgAuditexternal*preload★★★★★

shared_preload_libraries='pgaudit'restart
Then CREATE EXTENSION pgaudit;. Bundled/allow-listed by most distros, RDS, Aurora, Cloud SQL. Version tracks the server (pgAudit 18.x → PG18).
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';★
Session auditing. Classes: read, write, function, role, ddl, misc, misc_set, all. Writes to the server log (pair with log_line_prefix).
pgaudit.role = 'auditor'
Object auditing: grant that role privileges on the relations to watch — only SELECT/INSERT/UPDATE/DELETE touching them get logged.
pgaudit.log_catalog / log_parameter / log_relation
Fine-tune: include bind parameters, log each affected relation separately, or skip noisy catalog lookups.
volumecaution
log = 'all' or read on a busy system can flood logs — scope with pgaudit.role or per-database ALTER DATABASE … SET.

6citextcontrib★★★★★

CREATE EXTENSION citext;★
A case-insensitive text type — comparisons fold case automatically, so you stop scattering lower() everywhere.
CREATE TABLE users (email citext UNIQUE);★
The unique constraint now treats Foo@bar.com and foo@bar.com as the same value.
SELECT * WHERE email = 'Foo@Bar.com';
Matches regardless of case — and a plain b-tree index on the column is used directly.
locale-dependent foldingcaveat
Case-folding follows the DB collation and isn't identical to lower() for every Unicode string. For strict Unicode rules a nondeterministic collation is the modern alternative.

7postgres_fdwcontrib★★★★★

CREATE EXTENSION postgres_fdw;★
Query tables that live on another PostgreSQL server as if they were local.
CREATE SERVER srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'sales', port '5432');
Define the remote connection once.
CREATE USER MAPPING FOR current_user SERVER srv OPTIONS (user 'app', password '…');
Map a local role to remote credentials.
IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER srv INTO remote;★
Auto-create foreign tables. Or hand-write CREATE FOREIGN TABLE.
use_remote_estimate 'true' fetch_size
The planner pushes WHERE, joins and aggregates to the remote when it can — these options tune how aggressively.

8pg_cronexternalpreload★★★★★

shared_preload_libraries='pg_cron'restart
A cron scheduler that runs inside the database. Metadata lives in cron.database_name (default postgres). Then CREATE EXTENSION pg_cron;.
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');★
Standard 5-field cron. Also accepts sub-minute intervals like '30 seconds'.
SELECT cron.schedule_in_database('job','* * * * *','SQL','appdb');
Run a job against a database other than cron.database_name.
SELECT cron.unschedule('nightly-vacuum');
Inspect history in cron.job and cron.job_run_details.
one instance per jobnote
A job won't overlap itself — a slow run delays the next tick rather than running twice. Commonly paired with pg_partman for auto-partition maintenance.

9Native UUID / uuid-osspbuilt-in+contrib★★★★★

SELECT gen_random_uuid();★
Random UUIDv4 — built into core since PG13, no extension required. This is the default choice.
SELECT uuidv7();PG18
Time-ordered UUIDv7 — new in PG18. Sequential timestamps give far better index locality than v4. uuidv4() is a new alias for gen_random_uuid().
id uuid DEFAULT gen_random_uuid() PRIMARY KEY
Typical primary-key column. On PG18 prefer DEFAULT uuidv7() to avoid random-insert index bloat.
CREATE EXTENSION "uuid-ossp";
Only needed for the older variants: uuid_generate_v1(), _v3(), _v5() (MAC/name-based). Quote the hyphenated name.

10unaccentcontrib★★★★

CREATE EXTENSION unaccent;★
Strips diacritics so searches ignore accents — a text-search dictionary plus a callable function.
SELECT unaccent('Café Montréal');★
Returns Cafe Montreal. Use in WHERE unaccent(name) ILIKE unaccent('creme').
CREATE TEXT SEARCH CONFIGURATION … ; ALTER … ADD MAPPING … unaccent, simple;
Chain unaccent before the stemmer so full-text search is accent-insensitive too.
CREATE INDEX ON t (immutable_unaccent(name));IMMUTABLE
unaccent() is only STABLE, so it can't be indexed directly — wrap it in an IMMUTABLE SQL function to build a functional index.

11PostGISexternal★★★★ · see full section ↓

CREATE EXTENSION postgis;★
Turns PostgreSQL into a full spatial database: geometry/geography types, ~300 ST_ functions, GiST indexing.
SELECT ST_DWithin(a.geom, b.geom, 1000);
Predicates, measurement, transforms — the whole toolkit is in the dedicated PostGIS section (cards 28–33).
postgis_topology · postgis_raster · postgis_sfcgal
Companion extensions for topology, raster data and advanced 3-D (SFCGAL). Install as needed.
jump →
Open the PostGIS section for setup, SRID vs geography, constructors, spatial joins and indexing.

12pg_repackexternal★★★★ · 1.5.3

CREATE EXTENSION pg_repack;★
Rebuild bloated tables & indexes online — the low-lock alternative to VACUUM FULL / CLUSTER, which hold ACCESS EXCLUSIVE the whole time.
pg_repack -d mydb -t public.events★
Driven from a CLI client, not SQL. Repacks one table; repeat -t for several.
pg_repack -d mydb --no-order -i idx_name
--no-order = online VACUUM FULL (default for non-clustered tables); -i rebuilds a single index; -x orders by the CLUSTER index.
needs ~2× disk · brief final lockcaveat
Builds a full shadow copy, so free space ≥ table size is required; only a short ACCESS EXCLUSIVE lock at the final swap. Table needs a PRIMARY KEY / unique-not-null.

13pg_partmanexternal★★★★ · 5.x

CREATE EXTENSION pg_partman;★
Automates creation, retention and maintenance of declarative range/list partitions.
SELECT create_parent( p_parent_table := 'public.events', p_control := 'created_at', p_interval := '1 day', p_type := 'range');★
v5 uses named parameters. Also: p_premake (how many future partitions), p_template_table, p_default_table.
p_type := 'range' | 'list'v5 change
v5 is native-partitioning only — the old 'native' value and the trigger-based v4 API are gone. Don't follow pre-5.0 tutorials.
CALL run_maintenance_proc();
Creates upcoming partitions & applies retention. Automate it by adding pg_partman_bgw to shared_preload_libraries, or schedule via pg_cron.

14TimescaleDBexternal*preload★★★★

shared_preload_libraries='timescaledb'restart
Then CREATE EXTENSION timescaledb;. Turns a table into a time-partitioned hypertable with automatic chunking.
SELECT create_hypertable('metrics', 'time');★
Transparent partition-by-time; add a space dimension with add_dimension('metrics','device_id').
CREATE MATERIALIZED VIEW daily WITH (timescaledb.continuous) AS …★
Continuous aggregates: incrementally-maintained rollups. Automate with add_continuous_aggregate_policy().
add_compression_policy('metrics', INTERVAL '7 days');
Columnar compression (hypercore) for old chunks; add_retention_policy() drops chunks past a cutoff.
licensing / cloud availabilitycaveat
Core is Apache-2 but the headline features (compression, continuous aggregates, policies) are under the Timescale (TSL) license. Not on stock RDS — use Timescale Cloud or a provider that ships it.

15pgTAPexternal★★★★ · 1.3

CREATE EXTENSION pgtap;★
A unit-testing framework that lives in the database — assertions are SQL functions emitting TAP output.
SELECT plan(3); … SELECT * FROM finish();★
Declare how many tests, run them, then finish(). Wrap each run in a rolled-back transaction.
ok(1=1,'desc') is(a,b) throws_ok(…)
Value assertions. Plus schema assertions: has_table(), has_column(), col_is_pk(), has_index().
pg_prove -d mydb tests/*.sql
CLI harness (Perl TAP::Harness) to run a whole test suite and summarize pass/fail — drop it into CI.

16HypoPGexternal★★★★

CREATE EXTENSION hypopg;★
Hypothetical indexes: test whether an index would help without spending the time/disk to build it.
SELECT * FROM hypopg_create_index('CREATE INDEX ON t (col)');★
Registers a phantom index (session-local, in memory).
EXPLAIN SELECT … WHERE col = 42;★
The planner now considers the phantom index — if EXPLAIN picks it, the real one is worth building. (Plain EXPLAIN, not ANALYZE.)
hypopg_hide_index(oid) hypopg_list_indexes() hypopg_reset()
Also hide a real index to simulate dropping it; supports B-tree, BRIN, Hash and Bloom.

17pg_ivmexternalpreload★★★★ · 1.14

shared_preload_libraries='pg_ivm'restart
Then CREATE EXTENSION pg_ivm;. Adds Incrementally Maintained Materialized Views (IMMV) that update themselves as base tables change.
SELECT pgivm.create_immv('sales_mv', 'SELECT region, sum(amt) FROM sales GROUP BY 1');★
Builds the view and installs triggers. Reads hit it like a normal table — no stale data, no manual REFRESH.
INSERT INTO sales …; → sales_mv updates automatically
Unlike a standard MATERIALIZED VIEW, changes propagate immediately at commit.
SELECT pgivm.refresh_immv('sales_mv', true);
Full rebuild when you need one (true = repopulate with data). Supports aggregates, joins & DISTINCT with documented limits.

18hll (postgresql-hll)external★★★

CREATE EXTENSION hll;★
A hll data type (HyperLogLog) for approximate distinct counts in fixed, tiny storage — and it's mergeable.
CREATE TABLE daily (dt date, users hll);
Store one sketch per bucket (day, campaign, …) instead of raw IDs.
INSERT INTO daily SELECT dt, hll_add_agg(hll_hash_text(user_id)) …;★
Roll raw events into sketches with hll_add_agg over hashed values.
SELECT hll_cardinality(hll_union_agg(users)) FROM daily;★
Union sketches across any range then estimate uniques — e.g. monthly actives from daily rows, no re-scan of source data.

19hstorecontrib★★★

CREATE EXTENSION hstore;★
A single-level string → string key-value type — flat, fast, schemaless attributes in one column.
attrs hstore — 'color=>red, size=>M'::hstore
Literal syntax uses => between key and value.
attrs -> 'color' attrs ? 'color' attrs @> 'size=>M'★
-> fetch value, ? key exists, @> contains. Also akeys(), avals(), ?&/?|.
CREATE INDEX ON t USING gin (attrs);
GIN index accelerates ?/@> lookups. Convert with hstore_to_jsonb(attrs).
hstore vs jsonbnote
For nested/typed documents prefer jsonb. Reach for hstore when you want a simple, flat string map with minimal overhead.

20file_fdwcontrib★★★

CREATE EXTENSION file_fdw;★
Expose a flat file on the server (CSV, TSV) as a read-only table — no import step.
CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;
One local "server" to hang foreign tables off of.
CREATE FOREIGN TABLE logs (ts timestamptz, msg text) SERVER files OPTIONS (filename '/data/app.csv', format 'csv', header 'true');★
Query it with plain SELECT; the file is re-read each scan.
server-side path · superuser to createcaveat
The path is on the database server, not your client. Creating file_fdw tables needs superuser (or a granted role). OPTIONS (program '…') reads a command's output instead of a file.

28Setup & Geometry TypesPostGIS

CREATE EXTENSION IF NOT EXISTS postgis;★
Enable PostGIS in the current database.
POINT / LINESTRING / POLYGON★
The three basic OGC geometry types.
MULTIPOINT / MULTILINESTRING / MULTIPOLYGON
Collections of the same base type.
GEOMETRYCOLLECTION
A mixed bag of different geometry types.
'POINT(77.6 14.4)'★
WKT — Well-Known Text, the universal input format.
'SRID=4326;POINT(77.6 14.4)'
EWKT — PostGIS-extended WKT, embeds the SRID.

29SRID, geometry vs geographythe #1 source of bugs

SRID 4326★
WGS-84 lat/lon in degrees — GPS, web maps.
SRID 3857
Web Mercator in metres — what slippy maps render.
geometry★
Planar (flat) math — fast, full ~300-function library.
geography★
Spherical math — always metres, smaller function set.
ST_SetSRID(geom, srid)label only
Labels a geometry's SRID — does NOT reproject it.
ST_Transform(geom, srid)★
Actually reprojects to a new coordinate system.
ST_Distance(a, b)gotcha
Mixed SRIDs error out; on geometry 4326 the result is in degrees, not metres.

30Constructors & Accessorsbuild & inspect geometries

ST_MakePoint(x, y [,z])SRID 0
Raw coordinates — returns an unlabeled SRID.
ST_SetSRID(ST_MakePoint(lon,lat), 4326)★
The safe pattern — always wrap MakePoint like this.
ST_Point(x, y, srid)
Coordinates + SRID in one call.
ST_GeomFromText(wkt, srid)★
Parse a WKT string into a geometry.
ST_GeomFromGeoJSON(json)
Parse GeoJSON into a geometry.
ST_X(pt) / ST_Y(pt)
Read out a coordinate (geometry only).
ST_SRID(g) / ST_GeometryType(g)
Inspect a geometry's SRID / type.
ST_IsValid(g)★
OGC validity check (self-intersections, etc).
ST_AsText(g) / ST_AsEWKT(g)
Convert a geometry back to WKT / EWKT.

31Spatial Relationshipspredicates — see diagrams ↓

ST_Intersects(a, b)★
They share any point — the most common test.
ST_Contains(a, b) / ST_Within(a, b)★
a fully contains b / b lies fully within a.
ST_Covers(a, b) / ST_CoveredBy
Like contains, but boundary-inclusive (preferred).
ST_DWithin(a, b, d)★
Within distance d — index-aware "near me" query.
ST_Touches / ST_Crosses / ST_Overlaps
Boundary-only / partial / same-dimension overlap.
ST_Disjoint(a, b)slow
Share no point — a negation, not index-aware.

32Measurement & Processingcompute & transform

ST_Distance(a, b)★
Min distance — SRID units, or metres on geography.
ST_Length(line) / ST_Area(poly)★
Length of a line / area of a polygon.
ST_Perimeter(poly)
Boundary length of a polygon.
ST_Buffer(g, d)★
Zone within distance d of a geometry.
ST_Union(g) / ST_Intersection(a,b)
Merge geometries / compute their overlap.
ST_Centroid(g) / ST_Envelope(g)
Geometric center / bounding box.
ST_Simplify(g, tol)
Reduce vertex count for display/perf.
ST_MakeValid(g)★
Repair an invalid geometry before overlay ops.
ST_AsGeoJSON(g) / ST_AsMVT(…)
Web output: GeoJSON / vector tiles.

33Spatial Indexing & Loadingmake it fast, get data in

CREATE INDEX idx ON t USING GIST (geom);★
GiST R-Tree spatial index — required for performance.
geom && box
Bounding-box overlap operator — the index's native test.
geom <-> point
KNN distance operator, for "nearest N" queries.
shp2pgsql -s 4326 -I file.shp t | psql db★
Load a Shapefile, set source SRID, build an index.
ogr2ogr -f PostgreSQL PG:"dbname=db" file.geojson
Load GeoJSON / KML / many other formats via GDAL.
raster2pgsql -s 4326 -I file.tif t | psql db
Load a raster (imagery/DEM) into PostGIS.
pgsql2shp db -f out.shp t
Export a PostGIS table back to a Shapefile.

34Setup & TopologypgRouting

CREATE EXTENSION IF NOT EXISTS pgrouting;★
Enable pgRouting (it depends on PostGIS).
id, source, target, cost, reverse_cost, geom★
The columns every edge table needs before routing.
pgr_createTopology('edges', tol, 'geom', 'id')★
Auto-assign source/target node IDs from line endpoints.
pgr_analyzeGraph('edges', tol)
Report isolated segments, dead ends, and gaps in the topology.
pgr_nodeNetwork('edges', tol)
Split crossing lines into proper edges before building topology.

35Shortest PathpgRouting — see diagram ↓

pgr_dijkstra(sql, source, target)★
Classic single shortest-path algorithm — the default choice.
pgr_aStar(sql, source, target)
Heuristic-guided Dijkstra — faster, needs x/y in the edge query.
pgr_bdDijkstra(sql, source, target)
Bidirectional search from both ends — often faster on big graphs.
pgr_ksp(sql, source, target, k)
k alternate shortest paths, not just the single best one.
pgr_trsp(sql, source, target)
Shortest path that honors turn restrictions.

36Network AnalysispgRouting

pgr_drivingDistance(sql, source, cost)★
All nodes reachable within a cost budget — an isochrone.
pgr_withPoints(…)★
Route to/from arbitrary points, not just existing network nodes.
pgr_tsp(matrix_sql)
Best visiting order for a set of stops (travelling salesman).
pgr_findCloseEdges(edges_sql, point)
Snap an arbitrary point onto the nearest edge in the network.

★Reading the Placeholdersnotation used throughout

t, col, db, geom
a stand-in for a table / column / database / geometry-column name
'value'
a string literal — always single-quoted in SQL
value::type
PostgreSQL's inline cast shorthand
ST_*
PostGIS function prefix — "Spatial Type", from the OGC standard
pgr_*
pgRouting function prefix; sql is a live edge query, not a table name
[ … ]
optional clause; A|B means choose one

postgreSQL — extensions cheat sheet

Outline — jump to any section

Core & reference

IDRM extensions · 1–10

IDRM extensions · 11–20

Core SQL

PostgreSQL Extensions — IDRM Top 20

PostGIS — Spatial Extension

pgRouting — Network Routing

The four core joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

Reading the four common predicates

ST_Intersects

ST_Contains / ST_Within

ST_Touches

ST_DWithin

Worth memorizing