PostgreSQL

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.

About This PostgreSQL Cheat Sheet

This PostgreSQL cheat sheet covers all the essential SQL commands you need for working with Postgres databases. Whether you're learning PostgreSQL for the first time or need a quick syntax reference, this guide has you covered.

Each PostgreSQL command includes a working example that you can copy or run directly in your browser. The interactive SQL editor lets you experiment with queries against sample data without installing anything.

PostgreSQL offers powerful features beyond standard SQL, including window functions, CTEs, JSON support, arrays, and advanced indexing. This cheat sheet covers both common SQL patterns and PostgreSQL-specific features.

Looking for more? Check out our PostgreSQL tutorials and guides for in-depth explanations, or try DB Pro for a visual way to explore and edit your PostgreSQL databases.