PostgreSQL Cheatsheet

Connect to a PostgreSQL database

Connect using a connection string URL

Connect as local superuser

List all databases

Connect to (switch to) a database

List tables in the current schema

List all tables in all schemas

Describe table: columns, types, constraints

Verbose describe: includes indexes and triggers

List all indexes

List all views

List all functions

List all schemas

List all roles and users

List privileges for a table

Toggle display of query execution time

Toggle expanded (vertical) output mode

Open external editor for the current query

Execute SQL from a file

Send query output to a file

Client-side COPY (vs server-side — no superuser required)

Quit psql

Help for psql meta-commands

SQL syntax help for a command

Integer: -32,768 to 32,767 (2 bytes)

Integer: ~±2 billion (4 bytes)

Very large integer (8 bytes)

Auto-incrementing integer (shorthand for INT + sequence)

Auto-incrementing BIGINT

Exact decimal: p total digits, s decimal places — e.g. NUMERIC(10,2)

Floating-point numbers (4 / 8 bytes)

Unlimited-length character string (preferred in PostgreSQL)

Variable-length string with a maximum of n characters

Fixed-length string, blank-padded to n characters

Calendar date: YYYY-MM-DD

Date + time with timezone (preferred over TIMESTAMP)

Date + time without timezone

Time span — e.g. '1 year 2 months 3 days'

true / false / null

Universally unique identifier — generate with gen_random_uuid()

Binary JSON — indexable, faster querying (preferred over JSON)

Text JSON — stored as-is, slower for querying

Binary data (byte array)

Typed array — e.g. INTEGER[], TEXT[]

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')

IPv4/IPv6 address or network address

Create a new database

Delete a database

Create a new schema

Set the schema search path for the session

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  age        INTEGER CHECK (age >= 0),
  role       TEXT DEFAULT 'user',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  metadata   JSONB DEFAULT '{}'
);

Create a table with common column types and constraints

Add a column to an existing table

Drop a column from a table

Change a column's data type

Set a column default value

Rename a column

Rename a table

Delete all rows fast and reset sequences

Drop table and all dependent objects

Insert a single row

INSERT INTO users (email, name) VALUES
  ('a@b.com', 'Alice'),
  ('b@b.com', 'Bob');

Insert multiple rows at once

INSERT INTO users (id, email) VALUES (1, 'a@b.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

Upsert: insert or update on conflict

INSERT INTO users (id, email) VALUES (1, 'a@b.com')
ON CONFLICT (id) DO NOTHING;

Insert and silently ignore if conflict

Update columns for matching rows

Merge into a JSONB column

Update and return the modified rows

Delete rows and return them

Delete rows older than 30 days

Paginated SELECT with filtering and ordering

Select unique values

Aggregate functions

Group by with HAVING filter

Basic B-tree index

Unique index

Expression index (case-insensitive name lookup)

GIN index for JSONB queries

Partial index (only index rows matching condition)

Multi-column composite index

List indexes on a table

Drop index without locking the table

Show actual query execution plan and timings

Show execution plan including buffer usage

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

INNER JOIN — only matching rows from both tables

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

LEFT JOIN — all users, NULL for orders columns when no match

SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN — all rows from both sides with NULLs for missing matches

CROSS JOIN — Cartesian product of both tables

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

SELF JOIN — join a table to itself (e.g. org hierarchy)

SELECT u.name, recent.total
FROM users u
CROSS JOIN LATERAL (
  SELECT total FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 1
) recent;

LATERAL JOIN — correlated subquery as a join (useful for top-N per group)

WITH recent_orders AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, r.cnt FROM users u JOIN recent_orders r ON u.id = r.user_id;

CTE (Common Table Expression) — named subquery for readability

WITH RECURSIVE org AS (
  SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

Recursive CTE — traverse hierarchies and trees

Window function: sequential row numbers within each partition

Window function: rank with gaps (ties get same rank, next is skipped)

Window function: rank without gaps for tied values

Window function: access value from the previous row

Window function: access value from the next row

Window function: running total up to the current row

Window function: divide rows into N buckets (quartiles, deciles, etc.)

Window function: first value in the window frame

Get JSON value as JSON (object stays JSON)

Get JSON value as TEXT

Chain for nested JSON access

Path access — returns JSON

Path access — returns TEXT

JSONB contains — use with GIN index

JSONB key existence check

JSONB: all given keys exist?

JSONB: any given key exists?

JSONB merge/update a key

JSONB remove a key

Set a nested JSONB value

Expand JSONB object to key-value rows

List top-level keys of a JSONB object

Expand a JSONB array to rows

Return formatted (pretty-printed) JSONB

Create an array literal

Expand an array into individual rows

Aggregate values into an array

Split a delimited string into an array

Length of array dimension 1 (1-indexed in PostgreSQL)

Create a role with login capability

Create a user (role with LOGIN) with CREATEDB

Change a role's password

Delete a role (must revoke all grants first)

Assign a role to another role or user

Grant SELECT on a table to a role

Grant all privileges on all current tables

Revoke a privilege from a role

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Auto-grant SELECT to readonly on future tables

Enable Row Level Security (RLS) on a table

CREATE POLICY user_own_data ON users
  USING (id = current_user_id());

RLS policy: users can only see their own rows

Show the query plan (estimated costs)

Show plan with actual execution stats (runs the query)

Reclaim storage and update statistics for planner

Full vacuum — reclaims disk space but takes an exclusive lock

Rebuild all indexes on a table

Show total size of a table including indexes

Table access statistics (seq scans, row reads, etc.)

List currently running queries

Find long-running queries

Cancel a running query (sends SIGINT to backend)

Terminate a backend connection (sends SIGTERM)