PostgreSQL Cheat Sheet

A complete reference of PostgreSQL syntax with 61 interactive examples. Copy code snippets or click “Try It” to run them directly in your browser.

Creating & Modifying Tables

CREATE TABLE

Create a new table with columns and constraints.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Tip: Use SERIAL or BIGSERIAL for auto-incrementing IDs.

CREATE TABLE IF NOT EXISTS

Create a table only if it does not already exist.

CREATE TABLE IF NOT EXISTS logs (
  id SERIAL PRIMARY KEY,
  message TEXT,
  level TEXT DEFAULT 'info'
);

ALTER TABLE ADD

Add a new column to an existing table.

ALTER TABLE users ADD COLUMN phone TEXT;

ALTER TABLE ALTER COLUMN

Modify a column type or constraints.

ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);

ALTER TABLE RENAME

Rename a table or column.

ALTER TABLE users RENAME COLUMN name TO full_name;

DROP TABLE

Delete a table and all its data.

DROP TABLE IF EXISTS temp_data CASCADE;

Tip: Use CASCADE to also drop dependent objects.

CREATE INDEX

Create an index to speed up queries.

CREATE INDEX idx_users_email ON users(email);

Tip: PostgreSQL supports B-tree, Hash, GIN, GiST, and BRIN indexes.

CREATE VIEW

Create a virtual table based on a SELECT query.

CREATE VIEW active_users AS
SELECT id, name, email FROM users
WHERE created_at > NOW() - INTERVAL '30 days';

CREATE SCHEMA

Create a namespace for database objects.

CREATE SCHEMA sales;
CREATE TABLE sales.orders (id SERIAL PRIMARY KEY);

Tip: Schemas help organize tables and control access.

Adding & Changing Data

INSERT

Add new rows to a table.

INSERT INTO users (name, email)
VALUES ('Dave Wilson', 'dave@example.com')
RETURNING *;

Tip: Use RETURNING to get the inserted row back.

INSERT Multiple Rows

Insert multiple rows in a single statement.

INSERT INTO products (name, category, price, stock) VALUES
  ('Headphones', 'Electronics', 149.99, 100),
  ('Webcam', 'Electronics', 89.99, 60)
RETURNING *;

UPDATE

Modify existing rows in a table.

UPDATE orders SET status = 'shipped'
WHERE id = 3
RETURNING *;

Tip: Always use WHERE to avoid updating all rows.

DELETE

Remove rows from a table.

DELETE FROM orders WHERE status = 'pending'
RETURNING *;

Tip: Use RETURNING to see what was deleted.

UPSERT (INSERT ON CONFLICT)

Insert a row or update it if a conflict occurs.

INSERT INTO users (id, name, email)
VALUES (1, 'Alice Updated', 'alice.new@example.com')
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email
RETURNING *;

Tip: EXCLUDED refers to the row that would have been inserted.

TRUNCATE

Quickly remove all rows from a table.

-- TRUNCATE is faster than DELETE for removing all rows
-- TRUNCATE TABLE logs;

Tip: TRUNCATE is faster than DELETE but cannot be rolled back easily.

COPY

Bulk import/export data from files.

-- Import from CSV
-- COPY users FROM '/tmp/users.csv' WITH CSV HEADER;

-- Export to CSV
-- COPY (SELECT * FROM users) TO '/tmp/export.csv' WITH CSV HEADER;

Tip: Use \copy in psql for client-side file access.

Querying Data

SELECT

Retrieve data from one or more tables.

SELECT name, email FROM users;

SELECT *

Select all columns from a table.

SELECT * FROM products;

Tip: Avoid SELECT * in production; specify needed columns.

WHERE

Filter rows based on conditions.

SELECT * FROM orders WHERE price > 50 AND status = 'completed';

ORDER BY

Sort results by one or more columns.

SELECT * FROM products ORDER BY price DESC NULLS LAST;

Tip: PostgreSQL allows NULLS FIRST/LAST to control NULL ordering.

LIMIT & OFFSET

Limit the number of results and skip rows.

SELECT * FROM products ORDER BY price DESC LIMIT 3 OFFSET 1;

Tip: For large offsets, consider keyset pagination instead.

GROUP BY

Group rows by column values for aggregation.

SELECT status, COUNT(*) as count, SUM(price) as total
FROM orders GROUP BY status;

HAVING

Filter groups after GROUP BY (like WHERE for groups).

SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

DISTINCT / DISTINCT ON

Return only unique values.

SELECT DISTINCT category FROM products;

-- First product per category (PostgreSQL-specific)
-- SELECT DISTINCT ON (category) * FROM products ORDER BY category, price;

Tip: DISTINCT ON is a PostgreSQL extension for getting first row per group.

INNER JOIN

Return rows where there is a match in both tables.

SELECT users.name, orders.product, orders.price
FROM orders
INNER JOIN users ON orders.user_id = users.id;

LEFT JOIN

Return all rows from left table, with matches from right.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Tip: Unmatched right table columns will be NULL.

Subquery

Use a query inside another query.

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE price > 100);

CTE (WITH clause)

Define a temporary named result set for the query.

WITH high_value_orders AS (
  SELECT user_id, SUM(price) as total
  FROM orders
  GROUP BY user_id
  HAVING SUM(price) > 100
)
SELECT users.name, high_value_orders.total
FROM high_value_orders
JOIN users ON users.id = high_value_orders.user_id;

Tip: CTEs make complex queries more readable.

UNION

Combine results from multiple SELECT statements.

SELECT name, 'user' as type FROM users
UNION ALL
SELECT name, 'product' as type FROM products;

Tip: UNION removes duplicates; UNION ALL keeps them.

Window Functions

Perform calculations across related rows.

SELECT name, price,
  ROW_NUMBER() OVER (ORDER BY price DESC) as rank,
  SUM(price) OVER () as total
FROM products;

Tip: Window functions don't collapse rows like GROUP BY.

Built-in Functions

COUNT

Count the number of rows.

SELECT COUNT(*) as total_orders FROM orders;

SUM & AVG

Calculate the sum or average of a column.

SELECT
  SUM(price) as total_revenue,
  AVG(price) as avg_order_value
FROM orders;

MIN & MAX

Find the minimum or maximum value.

SELECT MIN(price) as cheapest, MAX(price) as most_expensive
FROM products;

COALESCE

Return the first non-NULL value.

SELECT name, COALESCE(email, 'no email') as email
FROM users;

Tip: Useful for providing default values for NULL columns.

NULLIF

Return NULL if two values are equal.

SELECT name, NULLIF(stock, 0) as stock_or_null
FROM products;

Tip: Useful for avoiding division by zero: x / NULLIF(y, 0)

CASE

Conditional logic in SQL (like if/else).

SELECT product, price,
  CASE
    WHEN price > 500 THEN 'expensive'
    WHEN price > 100 THEN 'moderate'
    ELSE 'affordable'
  END as price_tier
FROM orders;

String Functions

Manipulate text values.

SELECT
  UPPER(name) as uppercase,
  LENGTH(name) as name_length,
  SPLIT_PART(email, '@', 1) as username
FROM users;

Date Functions

Work with dates and times.

SELECT
  NOW() as current_time,
  CURRENT_DATE as today,
  CURRENT_DATE + INTERVAL '7 days' as next_week,
  EXTRACT(YEAR FROM NOW()) as current_year;

JSON Functions

Extract and manipulate JSON data.

SELECT '{"name": "Alice", "age": 30}'::jsonb->>'name' as name;

Tip: Use JSONB over JSON for better performance and indexing.

Array Functions

Work with PostgreSQL arrays.

SELECT ARRAY[1, 2, 3] as arr,
  ARRAY[1, 2, 3] @> ARRAY[2] as contains_two,
  array_length(ARRAY[1, 2, 3], 1) as len;

Tip: Arrays can be indexed with GIN for fast lookups.

generate_series

Generate a series of values.

SELECT generate_series(1, 5) as num;

-- Date series
-- SELECT generate_series(
--   '2024-01-01'::date,
--   '2024-01-07'::date,
--   '1 day'::interval
-- ) as day;

Tip: Great for generating test data or filling date gaps.

Data Rules & Constraints

PRIMARY KEY

Uniquely identify each row in a table.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER
);

Tip: Use SERIAL, BIGSERIAL, or GENERATED ALWAYS AS IDENTITY.

FOREIGN KEY

Link to a row in another table.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

Tip: ON DELETE options: CASCADE, SET NULL, SET DEFAULT, RESTRICT.

UNIQUE

Ensure all values in a column are different.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  username TEXT UNIQUE
);

NOT NULL

Prevent NULL values in a column.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

DEFAULT

Set a default value when none is provided.

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  message TEXT,
  level TEXT DEFAULT 'info',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CHECK

Validate data against a condition.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price DECIMAL CHECK (price > 0),
  stock INTEGER CHECK (stock >= 0)
);

EXCLUDE

Prevent overlapping values (PostgreSQL-specific).

-- Prevent overlapping date ranges
-- CREATE TABLE reservations (
--   room_id INTEGER,
--   during TSRANGE,
--   EXCLUDE USING gist (room_id WITH =, during WITH &&)
-- );

Tip: Requires btree_gist extension for non-range types.

Transactions

BEGIN

Start a new transaction.

BEGIN;

INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- Changes are not visible to other connections yet

Tip: PostgreSQL defaults to READ COMMITTED isolation.

COMMIT

Save all changes made in the transaction.

BEGIN;
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');
COMMIT;

SELECT * FROM users WHERE name = 'Test';

ROLLBACK

Undo all changes made in the transaction.

BEGIN;
DELETE FROM users;
ROLLBACK;

-- Users table is unchanged
SELECT * FROM users;

SAVEPOINT

Create a point to rollback to within a transaction.

BEGIN;
INSERT INTO users (name, email) VALUES ('A', 'a@test.com');
SAVEPOINT my_savepoint;
INSERT INTO users (name, email) VALUES ('B', 'b@test.com');
ROLLBACK TO SAVEPOINT my_savepoint;
COMMIT;

-- Only user 'A' was inserted
SELECT * FROM users WHERE email LIKE '%test.com';

LOCK / SELECT FOR UPDATE

Explicitly lock rows or tables.

-- Lock specific rows for update
-- SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- Skip already-locked rows (for queue processing)
-- SELECT * FROM jobs WHERE status = 'pending'
-- FOR UPDATE SKIP LOCKED LIMIT 1;

Tip: FOR UPDATE SKIP LOCKED is great for job queues.

Utilities & Advanced

Information Schema

Query database metadata.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

EXPLAIN ANALYZE

Show query execution plan with actual timing.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1;

Tip: Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for more detail.

VACUUM

Reclaim storage and update statistics.

-- Update statistics for query planner
-- VACUUM ANALYZE users;

-- Reclaim space (locks table)
-- VACUUM FULL users;

Tip: VACUUM FULL locks the table; regular VACUUM does not.

pg_stat Views

Monitor database activity and performance.

-- See active queries
-- SELECT pid, state, query FROM pg_stat_activity;

-- Table statistics
-- SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables;

Extensions

Add extra functionality to PostgreSQL.

-- Enable UUID generation
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enable full-text search
-- CREATE EXTENSION IF NOT EXISTS pg_trgm;

Tip: Popular: uuid-ossp, pgcrypto, pg_trgm, postgis.

LIKE & ILIKE

Pattern matching with wildcards.

SELECT * FROM users WHERE email ILIKE '%@example.com';

Tip: ILIKE is PostgreSQL-specific case-insensitive matching.

Regular Expressions

Match patterns using regex.

SELECT * FROM users WHERE email ~* '^[a-z]+@example\.com$';

Tip: PostgreSQL uses POSIX regex syntax.

LATERAL JOIN

Reference columns from preceding tables in subquery.

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

Tip: LATERAL lets subqueries reference earlier table columns.