PostgreSQL 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.
SUM(x) OVER (PARTITION BY c1 ORDER BY c2)
Window function: running/partitioned aggregate.

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

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

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

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

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

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

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

★Reading the Placeholdersnotation used throughout

t, col, db
a stand-in for a table / column / database name
'value'
a string literal — always single-quoted in SQL
value::type
PostgreSQL's inline cast shorthand
$1, $2
positional parameters inside functions
[ … ]
optional clause; A|B means choose one

postgreSQL cheat sheet

The four core joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

Worth memorizing