PostgreSQL + PostGIS + pgRouting

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.

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 + postGIS + pgRouting cheat sheet

Core SQL

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