PostgreSQL Cheat Sheet

Core PostgreSQL commands, SQL patterns, and object management for daily engineering work.

View
StandardDetailedCompact
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
## Connect and Inspect with psql
Connect to a database
psql -d appdb

# Open an interactive psql session to a database.

Connect with URI
psql postgresql://appuser:secret@localhost:5432/appdb

# Connect using a full PostgreSQL connection string.

List databases
\l

# Show all databases in the cluster.

List roles
\du

# Show roles and memberships.

List schemas
\dn

# Show schemas in the current database.

List tables views and sequences
\dt *.*

# List relations across schemas.

Describe a table
\d+ public.users

# Show columns, indexes, defaults, storage details, and more for a relation.

Toggle expanded output
\x auto

# Use expanded output automatically when rows are wide.

Toggle query timing
\timing on

# Show execution time after each statement.

Run SQL file
psql -d appdb -f schema.sql

# Execute commands from a SQL script file.

## Database and Role Lifecycle
Create database
CREATE DATABASE appdb;

# Create a new database in the cluster.

Create database with owner
CREATE DATABASE appdb OWNER appuser TEMPLATE template0 ENCODING 'UTF8';

# Create a database with explicit owner and template.

Rename database
ALTER DATABASE appdb RENAME TO appdb_prod;

# Rename an existing database.

Drop database
DROP DATABASE appdb;

# Delete a database permanently.

Create login role
CREATE ROLE appuser WITH LOGIN PASSWORD 'secret';

# Create a role that can log in.

Create role with privileges
CREATE ROLE readonly WITH LOGIN PASSWORD 'secret' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT;

# Create a role with explicit capability flags.

Change role password
ALTER ROLE appuser WITH PASSWORD 'new-secret';

# Update a role password.

Grant role membership
GRANT readonly TO analyst_user;

# Add a role as a member of another role.

Revoke role membership
REVOKE readonly FROM analyst_user;

# Remove inherited privileges from a member role.

Drop role
DROP ROLE readonly;

# Remove a role from the cluster.

## Schemas, Tables, and Core DDL
Create schema
CREATE SCHEMA accounting AUTHORIZATION appuser;

# Create a namespace for objects.

Set search path
SET search_path TO accounting, public;

# Change schema lookup order for the current session.

Create table
CREATE TABLE public.users (
  user_id bigserial PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

# Create a basic table with primary key and defaults.

Create identity column
CREATE TABLE events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

# Use a SQL-standard identity column instead of serial.

Add column
ALTER TABLE public.users ADD COLUMN last_login_at timestamptz;

# Add a nullable column to an existing table.

Set column default
ALTER TABLE public.users ALTER COLUMN created_at SET DEFAULT now();

# Change a column default value.

Set NOT NULL
ALTER TABLE public.users ALTER COLUMN email SET NOT NULL;

# Enforce non-nullability on a column.

Add check constraint
ALTER TABLE orders ADD CONSTRAINT orders_total_nonnegative CHECK (total_amount >= 0);

# Add a named CHECK constraint.

Rename table
ALTER TABLE public.users RENAME TO app_users;

# Rename a relation without recreating it.

Drop table
DROP TABLE IF EXISTS public.temp_import;

# Remove a table if it exists.

## Insert, Update, Delete, and Merge
Insert row
INSERT INTO public.users (email) VALUES ('a@example.com');

# Insert one row into a table.

Insert multiple rows
INSERT INTO public.users (email)
VALUES ('a@example.com'), ('b@example.com'), ('c@example.com');

# Insert many rows in one statement.

Insert with RETURNING
INSERT INTO public.users (email)
VALUES ('d@example.com')
RETURNING user_id, email, created_at;

# Insert rows and immediately return generated values.

Upsert with ON CONFLICT
INSERT INTO public.users (email)
VALUES ('a@example.com')
ON CONFLICT (email) DO UPDATE
SET created_at = now();

# Handle unique conflicts by updating instead of failing.

Update rows
UPDATE public.users SET last_login_at = now() WHERE user_id = 42;

# Modify matching rows.

Update from another table
UPDATE orders o
SET customer_tier = c.tier
FROM customers c
WHERE o.customer_id = c.customer_id;

# Update rows using another table in the same statement.

Delete rows
DELETE FROM sessions WHERE expires_at < now();

# Delete matching rows.

Delete with RETURNING
DELETE FROM jobs WHERE status = 'done' RETURNING job_id;

# Delete rows and return what was removed.

Merge source data
MERGE INTO inventory AS i
USING inventory_stage AS s
ON i.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

# Synchronize target data from a source relation with a single statement.

Truncate table
TRUNCATE TABLE audit_log RESTART IDENTITY;

# Quickly remove all rows and optionally reset identities.

## Querying, Joins, and Aggregation
Basic SELECT
SELECT user_id, email FROM public.users ORDER BY user_id DESC LIMIT 20;

# Fetch a sorted subset of rows.

Use DISTINCT ON
SELECT DISTINCT ON (customer_id)
  customer_id, order_id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

# Pick one row per group using PostgreSQL's `DISTINCT ON` extension.

Inner join
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

# Combine matching rows from two tables.

Left join
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

# Keep all rows from the left side even when no match exists.

Group and filter aggregates
SELECT customer_id, count(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING count(*) >= 5;

# Aggregate rows and then filter groups.

Window function row_number
SELECT *, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC)
FROM orders;

# Assign row numbers within each partition.

Common table expression
WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at >= now() - interval '7 days'
)
SELECT customer_id, count(*)
FROM recent_orders
GROUP BY customer_id;

# Use a CTE to structure a multi-step query.

Recursive CTE
WITH RECURSIVE nums(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

# Generate or traverse recursive data with `WITH RECURSIVE`.

Expand array values
SELECT user_id, unnest(tags) AS tag FROM user_profiles;

# Turn array elements into rows.

Extract JSONB field
SELECT payload->>'event_type' AS event_type FROM events;

# Extract a text value from a JSONB document.

## Indexes and Constraints
Create index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

# Add a basic B-tree index to speed lookups.

Create index concurrently
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

# Build an index with reduced write blocking.

Create unique index
CREATE UNIQUE INDEX idx_users_email_unique ON public.users (email);

# Enforce uniqueness with an index.

Create partial index
CREATE INDEX idx_orders_open_only ON orders (created_at) WHERE status = 'open';

# Index only a subset of rows.

Create expression index
CREATE INDEX idx_users_lower_email ON public.users ((lower(email)));

# Index an expression for case-insensitive lookups.

Create GIN index on JSONB
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);

# Accelerate JSONB containment queries.

Add primary key
ALTER TABLE invoices ADD CONSTRAINT invoices_pkey PRIMARY KEY (invoice_id);

# Define a primary key after table creation.

Add foreign key
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE RESTRICT;

# Link rows to a parent table with referential integrity.

Add unique constraint
ALTER TABLE public.users ADD CONSTRAINT users_email_key UNIQUE (email);

# Ensure unique values through a table constraint.

Drop index
DROP INDEX IF EXISTS idx_orders_open_only;

# Remove an index that is no longer needed.

## Views, Functions, Triggers, and Extensions
Create view
CREATE VIEW active_users AS
SELECT user_id, email
FROM public.users
WHERE deleted_at IS NULL;

# Create a reusable stored query.

Create materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day, sum(total_amount) AS revenue
FROM orders
GROUP BY 1;

# Persist query results for faster reads.

Refresh materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

# Update a materialized view's contents.

Create SQL function
CREATE FUNCTION public.user_email_domain(email text)
RETURNS text
LANGUAGE sql
AS $$
  SELECT split_part(email, '@', 2);
$$;

# Create a simple SQL-language function.

Create PL/pgSQL function
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# Create a PL/pgSQL trigger helper function.

Create trigger
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON public.users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

# Run a function automatically when rows change.

Enable extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

# Install an extension in the current database.

List installed extensions
\dx

# Show installed extensions and versions.

Add object comment
COMMENT ON TABLE public.users IS 'Application users';

# Document schema objects directly in the catalog.

Drop function
DROP FUNCTION IF EXISTS public.user_email_domain(text);

# Remove a function with its signature.

## JSONB, Arrays, and Date/Time Recipes
Build JSON object
SELECT jsonb_build_object('user_id', user_id, 'email', email) FROM public.users LIMIT 5;

# Construct JSONB on the fly in a query.

Update JSONB path
UPDATE events
SET payload = jsonb_set(payload, '{status}', '"processed"', true)
WHERE event_id = 1;

# Set or replace a value inside a JSONB document.

JSONB containment query
SELECT * FROM events WHERE payload @> '{"event_type":"signup"}'::jsonb;

# Find rows whose JSONB contains a given structure.

Append to array
UPDATE user_profiles SET tags = array_append(tags, 'vip') WHERE user_id = 42;

# Add an element to an array column.

Check array membership
SELECT * FROM user_profiles WHERE 'vip' = ANY(tags);

# Match rows when an array contains a value.

Bucket timestamps
SELECT date_trunc('hour', created_at) AS hour, count(*)
FROM events
GROUP BY 1
ORDER BY 1;

# Group events into time buckets.

Use interval arithmetic
SELECT now() - interval '30 days';

# Compute relative timestamps with intervals.

Convert timezone
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles'
FROM events
LIMIT 10;

# Convert timestamps between time zones.

Generate date series
SELECT generate_series(date '2026-01-01', date '2026-01-07', interval '1 day');

# Generate date or timestamp ranges without a table.

Use COALESCE
SELECT user_id, COALESCE(display_name, email) AS name FROM public.users;

# Provide fallback values for nullable columns.

Recommended next

No recommendations yet.