PostgreSQL Cheat Sheet

psql -d appdb

# Open an interactive psql session to a database.

psql postgresql://appuser:secret@localhost:5432/appdb

# Connect using a full PostgreSQL connection string.

\l

# Show all databases in the cluster.

\du

# Show roles and memberships.

\dn

# Show schemas in the current database.

\dt *.*

# List relations across schemas.

\d+ public.users

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

\x auto

# Use expanded output automatically when rows are wide.

\timing on

# Show execution time after each statement.

psql -d appdb -f schema.sql

# Execute commands from a SQL script file.

CREATE DATABASE appdb;

# Create a new database in the cluster.

CREATE DATABASE appdb OWNER appuser TEMPLATE template0 ENCODING 'UTF8';

# Create a database with explicit owner and template.

ALTER DATABASE appdb RENAME TO appdb_prod;

# Rename an existing database.

DROP DATABASE appdb;

# Delete a database permanently.

CREATE ROLE appuser WITH LOGIN PASSWORD 'secret';

# Create a role that can log in.

CREATE ROLE readonly WITH LOGIN PASSWORD 'secret' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT;

# Create a role with explicit capability flags.

ALTER ROLE appuser WITH PASSWORD 'new-secret';

# Update a role password.

GRANT readonly TO analyst_user;

# Add a role as a member of another role.

REVOKE readonly FROM analyst_user;

# Remove inherited privileges from a member role.

DROP ROLE readonly;

# Remove a role from the cluster.

CREATE SCHEMA accounting AUTHORIZATION appuser;

# Create a namespace for objects.

SET search_path TO accounting, public;

# Change schema lookup order for the current session.

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 TABLE events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

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

ALTER TABLE public.users ADD COLUMN last_login_at timestamptz;

# Add a nullable column to an existing table.

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

# Change a column default value.

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

# Enforce non-nullability on a column.

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

# Add a named CHECK constraint.

ALTER TABLE public.users RENAME TO app_users;

# Rename a relation without recreating it.

DROP TABLE IF EXISTS public.temp_import;

# Remove a table if it exists.

INSERT INTO public.users (email) VALUES ('a@example.com');

# Insert one row into a table.

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

# Insert many rows in one statement.

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

# Insert rows and immediately return generated values.

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 public.users SET last_login_at = now() WHERE user_id = 42;

# Modify matching rows.

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 FROM sessions WHERE expires_at < now();

# Delete matching rows.

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

# Delete rows and return what was removed.

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 audit_log RESTART IDENTITY;

# Quickly remove all rows and optionally reset identities.

SELECT user_id, email FROM public.users ORDER BY user_id DESC LIMIT 20;

# Fetch a sorted subset of rows.

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.

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.

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.

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

# Aggregate rows and then filter groups.

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

# Assign row numbers within each partition.

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.

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`.

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

# Turn array elements into rows.

SELECT payload->>'event_type' AS event_type FROM events;

# Extract a text value from a JSONB document.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

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

CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

# Build an index with reduced write blocking.

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

# Enforce uniqueness with an index.

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

# Index only a subset of rows.

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

# Index an expression for case-insensitive lookups.

CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);

# Accelerate JSONB containment queries.

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

# Define a primary key after table creation.

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.

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

# Ensure unique values through a table constraint.

DROP INDEX IF EXISTS idx_orders_open_only;

# Remove an index that is no longer needed.

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 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 CONCURRENTLY daily_sales;

# Update a materialized view's contents.

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 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 trg_users_updated_at
BEFORE UPDATE ON public.users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

# Run a function automatically when rows change.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

# Install an extension in the current database.

\dx

# Show installed extensions and versions.

COMMENT ON TABLE public.users IS 'Application users';

# Document schema objects directly in the catalog.

DROP FUNCTION IF EXISTS public.user_email_domain(text);

# Remove a function with its signature.

SELECT jsonb_build_object('user_id', user_id, 'email', email) FROM public.users LIMIT 5;

# Construct JSONB on the fly in a query.

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

# Set or replace a value inside a JSONB document.

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

# Find rows whose JSONB contains a given structure.

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

# Add an element to an array column.

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

# Match rows when an array contains a value.

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

# Group events into time buckets.

SELECT now() - interval '30 days';

# Compute relative timestamps with intervals.

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

# Convert timestamps between time zones.

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

# Generate date or timestamp ranges without a table.

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

# Provide fallback values for nullable columns.