PostgreSQL Cheat Sheet: Every Command You Need (2026)

Basic CRUD

The four fundamental operations in PostgreSQL. Every query you write uses at least one of them.

SELECT - Read data

-- Select specific columns
SELECT first_name, last_name, email
FROM users;

-- Select all columns
SELECT * FROM products;

-- Select with alias
SELECT first_name AS name, email AS contact
FROM users;

-- Select distinct values
SELECT DISTINCT department FROM employees;

INSERT INTO - Add new rows

-- Insert a single row
INSERT INTO users (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane@example.com');

-- Insert multiple rows
INSERT INTO users (first_name, last_name, email)
VALUES
    ('Alice', 'Johnson', 'alice@example.com'),
    ('Bob', 'Williams', 'bob@example.com');

-- Insert with RETURNING (PostgreSQL-specific)
INSERT INTO users (first_name, email)
VALUES ('Charlie', 'charlie@example.com')
RETURNING id, created_at;

UPDATE - Modify existing rows

-- Update specific rows
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 42;

-- Update multiple columns
UPDATE products
SET price = price * 1.10, updated_at = NOW()
WHERE category = 'electronics';

-- Update with RETURNING
UPDATE orders
SET status = 'shipped'
WHERE id = 100
RETURNING id, status, updated_at;

DELETE - Remove rows

-- Delete specific rows
DELETE FROM orders
WHERE status = 'cancelled' AND created_at < '2025-01-01';

-- Delete with RETURNING
DELETE FROM temp_logs
WHERE created_at < NOW() - INTERVAL '90 days'
RETURNING id, created_at;

Filtering and Sorting

Control which rows are returned and in what order. PostgreSQL supports all standard filtering plus ILIKE for case-insensitive matching.

WHERE, AND, OR

SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND (region = 'US' OR region = 'EU');

IN and BETWEEN

-- IN: match any value in a list
SELECT * FROM products
WHERE category IN ('electronics', 'books', 'clothing');

-- BETWEEN: inclusive range
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

LIKE and ILIKE

-- LIKE: case-sensitive pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- ILIKE: case-insensitive (PostgreSQL-specific)
SELECT * FROM users WHERE name ILIKE '%john%';

-- _ matches exactly one character
SELECT * FROM products WHERE sku LIKE 'SKU-____';

IS NULL

-- Find rows where a column is NULL
SELECT * FROM users WHERE phone IS NULL;

-- Find rows where a column is NOT NULL
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

ORDER BY and LIMIT/OFFSET

-- Sort ascending (default) and descending
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- Multiple sort columns
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

-- Pagination with LIMIT and OFFSET
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Page 3, 20 per page

Aggregation

Summarize data across multiple rows into a single result.

COUNT, SUM, AVG, MIN, MAX

SELECT
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM orders
WHERE order_date >= '2026-01-01';

GROUP BY

-- Revenue by category
SELECT
    category,
    COUNT(*) AS num_products,
    SUM(price) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC;

HAVING - Filter groups

-- Only show categories with more than 10 products
SELECT category, COUNT(*) AS num_products
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY num_products DESC;

Remember: WHERE filters rows before grouping. HAVING filters groups after aggregation.

JOINs

Combine rows from two or more tables. PostgreSQL supports all standard JOINs plus LATERAL JOIN for correlated subqueries in the FROM clause.

INNER JOIN - Only matching rows from both tables

SELECT o.id, o.order_date, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN - All rows from left table, matching from right

-- All customers, even those with no orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

RIGHT JOIN - All rows from right table, matching from left

-- All products, even those never ordered
SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.id
GROUP BY p.product_name;

FULL OUTER JOIN - All rows from both tables

SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

CROSS JOIN - Every combination of rows

-- Generate all size/color combinations
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

LATERAL JOIN (PostgreSQL-specific)

-- Get the 3 most recent orders per customer
SELECT c.name, recent.id, recent.total_amount, recent.order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.id, o.total_amount, o.order_date
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
    LIMIT 3
) recent ON true;

-- LATERAL lets the subquery reference columns from preceding tables
-- Much cleaner than correlated subqueries or window function workarounds

Quick reference: INNER = intersection, LEFT = all left + matching right, RIGHT = mirror of LEFT, FULL = union of both sides, CROSS = cartesian product, LATERAL = correlated subquery in FROM.

Subqueries and CTEs

Break complex queries into manageable parts. PostgreSQL CTEs support write operations, which is unique among databases.

Scalar Subquery

-- Use a subquery that returns a single value
SELECT name, salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Subquery with IN

-- Customers who placed orders over $500
SELECT name, email
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 500
);

EXISTS

-- Products that have been ordered at least once
SELECT p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.id
);

WITH (CTE)

-- Monthly revenue with running total
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_revenue
ORDER BY month;

Recursive CTE

-- Build an org chart hierarchy
WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees under each manager
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;

Writable CTEs (PostgreSQL-specific)

-- Archive old orders and delete them in one query
WITH archived AS (
    DELETE FROM orders
    WHERE status = 'completed'
      AND order_date < '2025-01-01'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

-- Update and return affected rows
WITH updated AS (
    UPDATE products
    SET price = price * 0.90
    WHERE category = 'clearance'
    RETURNING id, product_name, price
)
SELECT * FROM updated;

Window Functions

Perform calculations across a set of rows related to the current row, without collapsing them into a single output.

ROW_NUMBER, RANK, DENSE_RANK

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

ROW_NUMBER gives unique sequential numbers. RANK leaves gaps after ties (1,2,2,4). DENSE_RANK has no gaps (1,2,2,3).

NTILE

-- Divide employees into 4 salary quartiles
SELECT name, salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

LAG and LEAD

-- Compare each month's revenue to the previous month
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

SUM() OVER and ROWS BETWEEN

-- Running total
SELECT order_date, total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- 7-day moving average
SELECT order_date, total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

PostgreSQL-Specific Features

Features that set PostgreSQL apart from other databases.

SERIAL and GENERATED ALWAYS AS IDENTITY

-- Old way: SERIAL (still works)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Modern way: GENERATED ALWAYS AS IDENTITY (SQL standard)
CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

RETURNING clause

-- Get the inserted row back immediately
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, name, created_at;

-- Works with UPDATE and DELETE too
DELETE FROM sessions WHERE expires_at < NOW()
RETURNING user_id, session_id;

UPSERT (ON CONFLICT)

-- Insert or update if email already exists
INSERT INTO users (email, name, login_count)
VALUES ('alice@example.com', 'Alice', 1)
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    login_count = users.login_count + 1,
    last_login = NOW();

-- Insert or ignore duplicates
INSERT INTO tags (name)
VALUES ('postgresql'), ('sql'), ('database')
ON CONFLICT (name) DO NOTHING;

JSONB operators

-- -> returns JSON object, ->> returns text
SELECT
    data->'address' AS address_json,
    data->>'name' AS name_text,
    data->'address'->>'city' AS city
FROM users;

-- @> containment: does the JSONB contain this value?
SELECT * FROM events
WHERE metadata @> '{"type": "click"}';

-- ? key exists
SELECT * FROM products
WHERE attributes ? 'color';

-- ?| any key exists, ?& all keys exist
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

-- jsonb_each, jsonb_array_elements
SELECT key, value
FROM users, jsonb_each(data->'preferences');

Array operations

-- Create a table with an array column
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

-- Insert with arrays
INSERT INTO posts (title, tags)
VALUES ('PostgreSQL Tips', ARRAY['postgresql', 'database', 'sql']);

-- ANY: match any element in array
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- ALL: match all elements
SELECT * FROM products WHERE 100 > ALL(prices);

-- array_agg: aggregate rows into an array
SELECT department, array_agg(name ORDER BY name) AS members
FROM employees GROUP BY department;

-- unnest: expand array to rows
SELECT id, unnest(tags) AS tag FROM posts;

DISTINCT ON

-- Get the most recent order per customer (PostgreSQL-specific)
SELECT DISTINCT ON (customer_id)
    customer_id, id, total_amount, order_date
FROM orders
ORDER BY customer_id, order_date DESC;

generate_series()

-- Generate a sequence of dates (gap filling)
SELECT d::date AS day
FROM generate_series('2026-01-01', '2026-01-31', '1 day'::interval) d;

-- Generate numbers
SELECT generate_series(1, 100) AS n;

-- Fill gaps in time series data
SELECT d.day, COALESCE(o.order_count, 0) AS orders
FROM generate_series('2026-01-01', '2026-01-31', '1 day'::interval) d(day)
LEFT JOIN (
    SELECT order_date::date AS day, COUNT(*) AS order_count
    FROM orders GROUP BY order_date::date
) o ON d.day = o.day
ORDER BY d.day;

String Functions

Manipulate and transform text data in PostgreSQL.

-- CONCAT or || operator
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- UPPER / LOWER
SELECT UPPER(email) FROM users;
SELECT LOWER(city) FROM addresses;

-- SUBSTRING
SELECT SUBSTRING(phone FROM 1 FOR 3) AS area_code FROM contacts;

-- TRIM
SELECT TRIM(BOTH ' ' FROM name) FROM products;
SELECT LTRIM(code, '0') FROM items;  -- Remove leading zeros

-- LENGTH and REPLACE
SELECT name, LENGTH(name) AS name_length FROM products;
SELECT REPLACE(url, 'http://', 'https://') FROM links;

-- COALESCE: return first non-null value
SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

-- string_agg: aggregate strings with a delimiter
SELECT department,
    string_agg(name, ', ' ORDER BY name) AS team_members
FROM employees
GROUP BY department;

-- regexp_matches: extract with regex
SELECT (regexp_matches(email, '@(.+)$'))[1] AS domain
FROM users;

Date and Time

PostgreSQL has powerful date/time handling with INTERVAL arithmetic and precise timestamp functions.

-- Current date and time
SELECT CURRENT_DATE;          -- 2026-03-12
SELECT CURRENT_TIMESTAMP;     -- 2026-03-12 14:30:00.123+00
SELECT NOW();                 -- same as CURRENT_TIMESTAMP

-- DATE_TRUNC: round down to a unit
SELECT DATE_TRUNC('month', order_date) AS order_month FROM orders;
SELECT DATE_TRUNC('week', NOW()) AS start_of_week;

-- EXTRACT: pull out a date part
SELECT
    EXTRACT(YEAR FROM order_date) AS yr,
    EXTRACT(MONTH FROM order_date) AS mo,
    EXTRACT(DOW FROM order_date) AS day_of_week  -- 0=Sun, 6=Sat
FROM orders;

-- INTERVAL arithmetic
SELECT order_date,
    order_date + INTERVAL '7 days' AS one_week_later,
    order_date - INTERVAL '1 month' AS one_month_before
FROM orders;

-- age(): difference between dates as interval
SELECT name, age(NOW(), hire_date) AS tenure FROM employees;

-- to_char(): format dates
SELECT to_char(order_date, 'YYYY-MM-DD') AS formatted,
       to_char(order_date, 'Mon DD, YYYY') AS display_date,
       to_char(NOW(), 'HH24:MI:SS') AS current_time
FROM orders;

-- Filter by relative dates
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Generate a date series
SELECT d::date
FROM generate_series(
    DATE_TRUNC('month', CURRENT_DATE),
    DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day',
    '1 day'::interval
) d;

Table Operations

Define and modify your database structure. PostgreSQL supports advanced DDL features including custom types and extensions.

CREATE TABLE

CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    metadata JSONB DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true
);

ALTER TABLE

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Rename a column
ALTER TABLE users RENAME COLUMN first_name TO given_name;

-- Change column type
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(10,2);

-- Set/drop default
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT;

-- Add a constraint
ALTER TABLE orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

DROP TABLE and TRUNCATE

-- Drop if exists (safe)
DROP TABLE IF EXISTS temp_imports;

-- Drop with cascade (removes dependent objects)
DROP TABLE IF EXISTS old_orders CASCADE;

-- TRUNCATE: fast delete all rows (resets identity counters)
TRUNCATE TABLE logs;
TRUNCATE TABLE orders, order_items RESTART IDENTITY CASCADE;

CREATE INDEX (including GIN and GiST)

-- Standard B-tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- GIN index for JSONB (fast @>, ?, ?| queries)
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);

-- GIN index for array columns
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- GiST index for range/geometric types
CREATE INDEX idx_reservations_period ON reservations USING GiST (period);

-- Partial index (index only matching rows)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Drop an index
DROP INDEX IF EXISTS idx_users_email;

CREATE TYPE and CREATE EXTENSION

-- Create an enum type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered');

ALTER TABLE orders ADD COLUMN status order_status DEFAULT 'pending';

-- Create a composite type
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    state TEXT,
    zip TEXT
);

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";     -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pg_trgm";       -- Trigram similarity
CREATE EXTENSION IF NOT EXISTS "hstore";        -- Key-value pairs

-- Use uuid_generate_v4() after enabling uuid-ossp
SELECT uuid_generate_v4();

Pro Tips

Write faster, more efficient PostgreSQL queries.

EXPLAIN ANALYZE

-- See the actual query execution plan with timing
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- Include buffer usage stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Look for sequential scans on large tables. If you see one, an index on the JOIN or WHERE column usually fixes it.

pg_stat_statements

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find your slowest queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Index types at a glance

• B-tree (default): Equality and range queries (=, <, >, BETWEEN). Use for most columns.
• GIN: JSONB, arrays, full-text search. Supports @>, ?, @@ operators.
• GiST: Geometric data, range types, full-text search. Supports &&, @>, <@.
• BRIN: Very large, naturally ordered tables (e.g., append-only logs with timestamps). Tiny index, fast range scans.

VACUUM and maintenance

-- Manual vacuum (usually autovacuum handles this)
VACUUM ANALYZE users;

-- Full vacuum (reclaims disk space, locks table)
VACUUM FULL large_table;

-- Check table bloat
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Partitioning

-- Range partitioning by date
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    order_date DATE NOT NULL,
    customer_id INT,
    total_amount NUMERIC(10,2)
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE orders_2026 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Queries automatically target the right partition
SELECT * FROM orders WHERE order_date = '2026-03-12';

Try these queries instantly -- AI2SQL generates PostgreSQL from plain English so you can skip the syntax and focus on the data you need.

Frequently Asked Questions

What makes PostgreSQL different from standard SQL?

PostgreSQL extends standard SQL with features like JSONB data types, array columns, LATERAL JOINs, DISTINCT ON, RETURNING clauses, UPSERT (ON CONFLICT), recursive CTEs with write operations, advanced indexing (GIN, GiST, BRIN), and built-in full-text search. These features make it one of the most powerful open-source relational databases.

What is the difference between JSON and JSONB in PostgreSQL?

JSON stores data as plain text and preserves formatting, while JSONB stores data in a binary format that is faster to query and supports indexing. Use JSONB in almost all cases -- it supports operators like @> (containment), ? (key exists), and can be indexed with GIN indexes for fast lookups.

How do I use UPSERT in PostgreSQL?

PostgreSQL implements UPSERT via INSERT ... ON CONFLICT. You specify the conflict target (a unique constraint or column) and what to do on conflict -- either DO NOTHING or DO UPDATE SET. Example: INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

What are the best PostgreSQL index types to use?

B-tree is the default and best for equality and range queries. GIN indexes are ideal for JSONB, arrays, and full-text search. GiST indexes work well for geometric data and range types. BRIN indexes are extremely compact and suited for naturally ordered data like timestamps in append-only tables.

Can AI generate PostgreSQL queries for me?

Yes. Tools like AI2SQL let you describe what you need in plain English and generate correct PostgreSQL syntax automatically. This is especially useful for complex queries involving JSONB operations, window functions, CTEs, or LATERAL JOINs. Understanding the fundamentals from a cheat sheet helps you verify AI-generated queries.

Related Resources

• SQL Cheat Sheet: Every Command You Need in One Page -- the general SQL reference covering all databases
• SQL JOINs Explained -- visual guide to INNER, LEFT, RIGHT, and FULL JOINs
• What is SQL? -- beginner introduction to SQL and relational databases
• MySQL to PostgreSQL Converter -- automatically convert MySQL syntax to PostgreSQL