PostgreSQL + PostGIS 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.

06Core 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.

07Query 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.

08FilteringWHERE 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.

09Joinscombine 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.

10Aggregation & 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.

11Window 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.

12Modifying Datawrite operations

INSERT INTO t (c1,c2) VALUES (v1,v2);★
Add a row.
… ON CONFLICT (id) DO UPDATE SET …;★
Upsert: insert, or update on key clash.
UPDATE t SET c=v WHERE condn;★
Modify matching rows.
DELETE FROM t WHERE condn;★
Remove matching rows.
DELETE FROM t;care
Removes every row (logged, row-by-row).
COPY t FROM 'file' CSV HEADER;
Bulk-load rows from a CSV file.

13Transactionsall-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.

14Indexes & 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.

15Roles & 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.
DROP ROLE IF EXISTS r;care
Delete a role permanently.

16Text, 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.

17Backup, 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.

18Monitoring & 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.

19Setup & 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.

20SRID, 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.

21Constructors & 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.

22Spatial 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.

23Measurement & 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.

24Spatial 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.

★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
[ … ]
optional clause; A|B means choose one

postgreSQL + postGIS cheat sheet

Core SQL

PostGIS — Spatial Extension

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