PostgreSQL Cheat Sheet

A complete reference of PostgreSQL commands with examples and usage instructions. Find the command you need using the search bar or browse by category.

🐘 What is PostgreSQL?

PostgreSQL is a powerful, open-source object-relational database system with over 35 years of active development. Known for its reliability, feature robustness, and performance, PostgreSQL is the database of choice for many enterprises, startups, and government agencies worldwide.

📈 Key Features

ACID Compliant: Full transaction support with serializable isolation
Advanced Types: JSONB, arrays, UUID, hstore, range types
Window Functions: ROW_NUMBER, RANK, LAG, LEAD, and more
Full-Text Search: Built-in tsvector and tsquery support
Extensible: Custom types, operators, functions, and extensions

🚀 PostgreSQL-Specific Features

RETURNING: Get data back from INSERT/UPDATE/DELETE
ILIKE: Case-insensitive pattern matching
ON CONFLICT: Native upsert support
JSONB: Binary JSON with indexing and operators
EXPLAIN ANALYZE: Detailed query performance profiling

🌐 Who Uses PostgreSQL?

Tech Companies:

Apple, Instagram, Spotify
Reddit, Twitch, Discord

Cloud Platforms:

AWS RDS, Aurora
Supabase, Neon, Railway

Compatible Forks:

CockroachDB, YugabyteDB
TimescaleDB, Citus

53 commands found

Filter by category:

`SELECT`

Queries

Retrieve rows from one or more tables

Syntax:

SELECT column1, column2 FROM table_name WHERE condition ORDER BY column LIMIT n;

Examples:

SELECT * FROM users;

Select all columns from users

SELECT name, email FROM users WHERE active = true;

Filter rows by condition

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

Latest 10 rows

SELECT DISTINCT country FROM users;

Unique values only

Notes:

PostgreSQL supports all standard SELECT clauses including DISTINCT ON, which returns the first row for each distinct value.

`INSERT`

Queries

Add new rows to a table

Syntax:

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

Examples:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

Insert single row

INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');

Insert multiple rows

INSERT INTO users (name, email) VALUES ('Dave', 'dave@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Upsert with ON CONFLICT

INSERT INTO users (name, email) VALUES ('Eve', 'eve@example.com') ON CONFLICT DO NOTHING;

Skip insert on conflict

Notes:

ON CONFLICT DO UPDATE (upsert) is PostgreSQL-specific. EXCLUDED refers to the row that was proposed for insertion.

`UPDATE`

Queries

Modify existing rows in a table

Syntax:

UPDATE table_name SET col1 = val1 WHERE condition;

Examples:

UPDATE users SET email = 'new@example.com' WHERE id = 1;

Update one row

UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

Increase price by 10%

UPDATE users SET last_login = NOW() WHERE id = 42;

Set timestamp to current time

Notes:

Always include a WHERE clause -- omitting it updates every row in the table.

`DELETE`

Queries

Remove rows from a table

Syntax:

DELETE FROM table_name WHERE condition;

Examples:

DELETE FROM users WHERE id = 5;

Delete a specific row

DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';

Delete rows older than 30 days

DELETE FROM users;

Delete all rows (use with caution)

Notes:

Use TRUNCATE for faster bulk deletion when you do not need row-level triggers to fire.

`RETURNING`

Queries

Return data from rows affected by INSERT, UPDATE, or DELETE

Syntax:

INSERT INTO table_name (cols) VALUES (vals) RETURNING *;

Examples:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com') RETURNING id, created_at;

Get auto-generated id after insert

UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year' RETURNING id, name;

See which rows were updated

DELETE FROM sessions WHERE expires_at < NOW() RETURNING user_id;

Return deleted rows

Notes:

RETURNING is PostgreSQL-specific. It avoids a separate SELECT after write operations.

`WHERE`

Filtering

Filter rows based on a condition

Syntax:

SELECT * FROM table_name WHERE condition;

Examples:

SELECT * FROM users WHERE active = true;

Simple boolean filter

SELECT * FROM orders WHERE status = 'shipped' AND total > 100;

Multiple conditions with AND

SELECT * FROM users WHERE country = 'US' OR country = 'CA';

Either condition with OR

Notes:

WHERE is evaluated before GROUP BY. Use HAVING to filter after aggregation.

`IN / BETWEEN`

Filtering

Filter by a set of values or a range

Syntax:

SELECT * FROM table WHERE col IN (val1, val2);
SELECT * FROM table WHERE col BETWEEN low AND high;

Examples:

SELECT * FROM users WHERE country IN ('US', 'CA', 'UK');

Match any value in the list

SELECT * FROM orders WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

Date range filter

SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;

Exclude a range

Notes:

BETWEEN is inclusive of both endpoints. IN can also accept a subquery.

`LIKE / ILIKE`

Filtering

Pattern matching on text columns

Syntax:

SELECT * FROM table WHERE col LIKE 'pattern';
SELECT * FROM table WHERE col ILIKE 'pattern';

Examples:

SELECT * FROM users WHERE name LIKE 'A%';

Names starting with A (case-sensitive)

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

Gmail addresses (case-insensitive)

SELECT * FROM products WHERE name ILIKE '%phone%';

Contains phone (case-insensitive)

Notes:

ILIKE is PostgreSQL-specific and performs case-insensitive matching. Use % for any characters, _ for a single character.

`IS NULL / IS NOT NULL`

Filtering

Check for NULL values

Syntax:

SELECT * FROM table WHERE col IS NULL;
SELECT * FROM table WHERE col IS NOT NULL;

Examples:

SELECT * FROM users WHERE deleted_at IS NULL;

Find active (non-deleted) rows

SELECT * FROM orders WHERE shipped_at IS NOT NULL;

Find shipped orders

SELECT COALESCE(nickname, name, 'Anonymous') FROM users;

Return first non-null value

Notes:

Never use = NULL or != NULL. Always use IS NULL / IS NOT NULL. COALESCE returns the first non-null argument.

`INNER JOIN`

Joins

Return rows that have matching values in both tables

Syntax:

SELECT cols FROM t1 INNER JOIN t2 ON t1.id = t2.fk;

Examples:

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Only users who have orders

Notes:

INNER JOIN is the default JOIN type. Rows with no match in either table are excluded.

`LEFT JOIN`

Joins

Return all rows from the left table, with NULLs for non-matching right rows

Syntax:

SELECT cols FROM t1 LEFT JOIN t2 ON t1.id = t2.fk;

Examples:

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

All users, NULL if no orders

SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Users who have never ordered

`RIGHT JOIN`

Joins

Return all rows from the right table, with NULLs for non-matching left rows

Syntax:

SELECT cols FROM t1 RIGHT JOIN t2 ON t1.id = t2.fk;

Examples:

SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

All orders, even if the user was deleted

Notes:

RIGHT JOIN is less common. Most queries can be rewritten as LEFT JOIN by swapping table order.

`FULL OUTER JOIN`

Joins

Return all rows from both tables, with NULLs where there is no match

Syntax:

SELECT cols FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.fk;

Examples:

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

All employees and departments, including unmatched

Notes:

FULL OUTER JOIN is supported in PostgreSQL (unlike SQLite). Useful for finding orphan records on both sides.

`CROSS JOIN`

Joins

Return the Cartesian product of two tables

Syntax:

SELECT cols FROM t1 CROSS JOIN t2;

Examples:

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Generate all size-color combinations

Notes:

CROSS JOIN produces rows = (rows in t1) x (rows in t2). Use with caution on large tables.

`COUNT / SUM / AVG`

Aggregates

Aggregate functions for counting, summing, and averaging

Syntax:

SELECT COUNT(*), SUM(col), AVG(col) FROM table;

Examples:

SELECT COUNT(*) FROM users;

Total row count

SELECT SUM(total) FROM orders WHERE user_id = 1;

Total spent by user 1

SELECT AVG(price) FROM products;

Average product price

SELECT COUNT(DISTINCT country) FROM users;

Number of unique countries

`MIN / MAX`

Aggregates

Find the smallest or largest value in a column

Syntax:

SELECT MIN(col), MAX(col) FROM table;

Examples:

SELECT MIN(price), MAX(price) FROM products;

Price range

SELECT MAX(created_at) FROM orders WHERE user_id = 1;

Most recent order date

`GROUP BY / HAVING`

Aggregates

Group rows and filter groups with aggregate conditions

Syntax:

SELECT col, AGG(col2) FROM table GROUP BY col HAVING condition;

Examples:

SELECT country, COUNT(*) AS total FROM users GROUP BY country;

Count users by country

SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 50;

Categories with avg price above 50

SELECT user_id, SUM(total) AS spent FROM orders GROUP BY user_id HAVING SUM(total) > 1000 ORDER BY spent DESC;

Top spenders

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) FROM orders GROUP BY month ORDER BY month;

Orders per month

Notes:

HAVING filters after aggregation, WHERE filters before. DATE_TRUNC is PostgreSQL-specific.

`ROW_NUMBER`

Window Functions

Assign a unique sequential integer to each row within a partition

Syntax:

SELECT ROW_NUMBER() OVER (PARTITION BY col ORDER BY col2) FROM table;

Examples:

SELECT name, department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Rank employees within each department by salary

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) sub WHERE rn = 1;

Latest order per user

Notes:

ROW_NUMBER always produces unique numbers. For ties, use RANK or DENSE_RANK.

`RANK / DENSE_RANK`

Window Functions

Assign ranks to rows, handling ties differently

Syntax:

SELECT RANK() OVER (ORDER BY col) FROM table;
SELECT DENSE_RANK() OVER (ORDER BY col) FROM table;

Examples:

SELECT name, score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

RANK skips numbers after ties, DENSE_RANK does not

Notes:

With scores 100, 90, 90, 80: RANK gives 1,2,2,4 and DENSE_RANK gives 1,2,2,3.

`LAG / LEAD`

Window Functions

Access a row before or after the current row without a self-join

Syntax:

SELECT LAG(col, offset, default) OVER (ORDER BY col2) FROM table;
SELECT LEAD(col, offset, default) OVER (ORDER BY col2) FROM table;

Examples:

SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS growth
FROM daily_sales;

Compare each day to the previous day

SELECT name, hired_at,
  LEAD(name) OVER (ORDER BY hired_at) AS next_hire
FROM employees;

Show who was hired next

Notes:

LAG looks backward, LEAD looks forward. The second argument is the offset (default 1), the third is the default value if no row exists.

`WITH (CTE)`

CTEs

Define a named temporary result set for reuse in a query

Syntax:

WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

Examples:

WITH top_users AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 500
)
SELECT u.name, t.spent
FROM users u
JOIN top_users t ON u.id = t.user_id;

Top spenders using a CTE

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly;

Running total of monthly revenue

Notes:

CTEs improve readability. In PostgreSQL 12+, CTEs can be inlined by the optimizer unless marked WITH ... AS MATERIALIZED.

`WITH RECURSIVE`

CTEs

Recursive common table expression for hierarchical data

Syntax:

WITH RECURSIVE cte AS (
  SELECT ... -- base case
  UNION ALL
  SELECT ... FROM cte WHERE ... -- recursive step
) SELECT * FROM cte;

Examples:

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth;

Traverse an employee hierarchy

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

Generate a number sequence 1 to 10

Notes:

Always include a termination condition to avoid infinite recursion.

`Subquery`

Subqueries

Use a nested SELECT as a value, list, or table

Syntax:

SELECT * FROM table WHERE col IN (SELECT col FROM other_table);

Examples:

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE country = 'US');

IN subquery

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

Scalar subquery comparison

SELECT u.name, sub.total
FROM users u
JOIN (SELECT user_id, SUM(total) AS total FROM orders GROUP BY user_id) sub
  ON u.id = sub.user_id;

Derived table subquery

`EXISTS / NOT EXISTS`

Subqueries

Test whether a subquery returns any rows

Syntax:

SELECT * FROM table WHERE EXISTS (SELECT 1 FROM other WHERE ...);

Examples:

SELECT u.name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Users who have at least one order

SELECT p.name FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

Products that have never been ordered

Notes:

EXISTS is often faster than IN for correlated subqueries because it stops at the first match.

`CREATE TABLE`

Table DDL

Define a new table with columns and constraints

Syntax:

CREATE TABLE table_name (
  col1 TYPE CONSTRAINT,
  col2 TYPE,
  ...
);

Examples:

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT UNIQUE NOT NULL,
  active     BOOLEAN DEFAULT true,
  metadata   JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Typical users table with PG-specific types

CREATE TABLE IF NOT EXISTS settings (
  key   TEXT PRIMARY KEY,
  value JSONB
);

Create only if it does not exist

Notes:

PostgreSQL enforces strict types (unlike SQLite). Prefer BIGSERIAL over SERIAL for new tables.

`ALTER TABLE`

Table DDL

Modify an existing table structure

Syntax:

ALTER TABLE table_name ADD COLUMN col_name TYPE;
ALTER TABLE table_name DROP COLUMN col_name;
ALTER TABLE table_name RENAME COLUMN old TO new;

Examples:

ALTER TABLE users ADD COLUMN phone TEXT;

Add a new column

ALTER TABLE users DROP COLUMN phone;

Remove a column

ALTER TABLE users RENAME COLUMN phone TO mobile;

Rename a column

ALTER TABLE users ALTER COLUMN name SET NOT NULL;

Add NOT NULL constraint

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

Change column type

Notes:

PostgreSQL ALTER TABLE is much more powerful than SQLite. You can add, drop, and rename columns, change types, and modify constraints.

`DROP TABLE`

Table DDL

Permanently remove a table and all its data

Syntax:

DROP TABLE [IF EXISTS] table_name [CASCADE];

Examples:

DROP TABLE old_logs;

Remove a table

DROP TABLE IF EXISTS temp_cache;

Remove only if exists

DROP TABLE orders CASCADE;

Drop table and all dependent objects

Notes:

CASCADE also drops objects that depend on the table (views, foreign keys). Use with caution.

`TRUNCATE`

Table DDL

Quickly remove all rows from a table

Syntax:

TRUNCATE TABLE table_name [CASCADE];

Examples:

TRUNCATE TABLE logs;

Remove all rows instantly

TRUNCATE TABLE orders, order_items CASCADE;

Truncate multiple related tables

TRUNCATE TABLE users RESTART IDENTITY;

Reset auto-increment sequences

Notes:

TRUNCATE is faster than DELETE for large tables because it does not scan rows. It does not fire row-level triggers.

`CREATE INDEX`

Indexes

Create an index to speed up queries on a column

Syntax:

CREATE [UNIQUE] INDEX [CONCURRENTLY] index_name ON table_name (col1, col2);

Examples:

CREATE INDEX idx_users_email ON users (email);

B-tree index for email lookups

CREATE INDEX CONCURRENTLY idx_orders_date ON orders (created_at);

Create index without locking the table

CREATE INDEX idx_users_name_gin ON users USING gin (to_tsvector('english', name));

GIN index for full-text search

Notes:

CONCURRENTLY avoids locking the table during index creation. Always use it in production.

`CREATE UNIQUE INDEX`

Indexes

Create an index that also enforces uniqueness

Syntax:

CREATE UNIQUE INDEX index_name ON table_name (col1, col2);

Examples:

CREATE UNIQUE INDEX idx_users_username ON users (username);

Enforce unique usernames

CREATE UNIQUE INDEX idx_user_email_lower ON users (LOWER(email));

Case-insensitive unique email

Notes:

Expression indexes like LOWER(email) ensure uniqueness regardless of case.

`Partial Index`

Indexes

Create an index on a subset of rows using a WHERE clause

Syntax:

CREATE INDEX index_name ON table_name (col) WHERE condition;

Examples:

CREATE INDEX idx_active_users ON users (email) WHERE active = true;

Index only active users

CREATE INDEX idx_unshipped ON orders (created_at) WHERE shipped_at IS NULL;

Index only unshipped orders

Notes:

Partial indexes are smaller and faster than full indexes. Great for queries that always filter on the same condition.

`PRIMARY KEY`

Constraints

Uniquely identify each row in a table

Syntax:

col_name TYPE PRIMARY KEY
-- or as table constraint:
CONSTRAINT pk_name PRIMARY KEY (col1, col2)

Examples:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Single column primary key

CREATE TABLE order_items (
  order_id BIGINT,
  product_id BIGINT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

Composite primary key

Notes:

PRIMARY KEY implies UNIQUE and NOT NULL. Use BIGSERIAL or UUID for auto-generated keys.

`FOREIGN KEY`

Constraints

Enforce referential integrity between tables

Syntax:

col_name TYPE REFERENCES parent_table(col)
-- or:
CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(col) ON DELETE CASCADE

Examples:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id)
);

Inline foreign key

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Named FK with cascade delete

Notes:

ON DELETE CASCADE automatically deletes child rows. Other options: SET NULL, SET DEFAULT, RESTRICT, NO ACTION.

`UNIQUE / CHECK / DEFAULT`

Constraints

Enforce uniqueness, validation rules, and default values

Syntax:

col TYPE UNIQUE
col TYPE CHECK (condition)
col TYPE DEFAULT value

Examples:

CREATE TABLE products (
  sku TEXT UNIQUE NOT NULL,
  price NUMERIC CHECK (price >= 0),
  status TEXT DEFAULT 'draft'
);

Multiple constraints on columns

ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 150);

Add a check constraint to existing table

Notes:

CHECK constraints are evaluated for every INSERT and UPDATE. They cannot reference other tables.

`-> / ->> / @>`

JSON/JSONB

Access and query JSONB data with operators

Syntax:

col -> 'key'        -- returns JSONB
col ->> 'key'       -- returns TEXT
col @> '{"key": "val"}'  -- contains

Examples:

SELECT data -> 'address' -> 'city' FROM users;

Navigate nested JSON (returns JSONB)

SELECT data ->> 'name' FROM users;

Extract value as text

SELECT * FROM users WHERE data @> '{"role": "admin"}';

Find rows where JSON contains key-value pair

SELECT * FROM events WHERE tags ? 'urgent';

Check if JSONB contains a key

Notes:

-> returns JSONB, ->> returns TEXT. Use @> with a GIN index for fast containment queries.

`jsonb_set / json_agg / jsonb_build_object`

JSON/JSONB

Manipulate and aggregate JSON data

Syntax:

jsonb_set(target, path, new_value)
json_agg(expression)
jsonb_build_object(key1, val1, ...)

Examples:

UPDATE users SET data = jsonb_set(data, '{address,city}', '"New York"') WHERE id = 1;

Update a nested JSON field

SELECT department, json_agg(jsonb_build_object('name', name, 'salary', salary)) FROM employees GROUP BY department;

Aggregate rows into a JSON array

SELECT jsonb_build_object('id', id, 'name', name, 'email', email) FROM users;

Build JSON object from columns

SELECT * FROM users, jsonb_each(data) AS kv(key, value);

Expand JSONB object into key-value rows

Notes:

JSONB is stored in a binary format. It is faster to query than JSON but slightly slower to insert.

`ANY / ALL`

Arrays

Compare a value against array elements

Syntax:

SELECT * FROM table WHERE val = ANY(array_col);
SELECT * FROM table WHERE val = ALL(array_col);

Examples:

SELECT * FROM users WHERE 'admin' = ANY(roles);

Find users with admin role

SELECT * FROM products WHERE id = ANY(ARRAY[1, 2, 3]);

Match any id in the array

SELECT * FROM scores WHERE 100 <= ALL(attempts);

All attempts scored 100 or above

Notes:

ANY returns true if at least one element matches. ALL returns true only if every element matches.

`array_agg / unnest`

Arrays

Aggregate values into an array or expand an array into rows

Syntax:

SELECT array_agg(col) FROM table;
SELECT unnest(array_col) FROM table;

Examples:

SELECT department, array_agg(name ORDER BY name) FROM employees GROUP BY department;

Collect names into a sorted array per department

SELECT unnest(tags) AS tag FROM posts;

Expand an array column into individual rows

SELECT unnest(ARRAY['a','b','c']) AS letter;

Expand a literal array into rows

Notes:

array_agg can include ORDER BY inside the aggregate. unnest is the inverse operation.

`BEGIN / COMMIT / ROLLBACK`

Transactions

Group multiple statements into an atomic unit

Syntax:

BEGIN;
  -- statements
COMMIT; -- or ROLLBACK;

Examples:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Transfer funds atomically

BEGIN;
  DELETE FROM users WHERE id = 99;
ROLLBACK;

Undo changes with ROLLBACK

Notes:

PostgreSQL supports full ACID transactions. All statements inside BEGIN...COMMIT succeed or fail together.

`SAVEPOINT`

Transactions

Create a checkpoint within a transaction for partial rollback

Syntax:

SAVEPOINT name;
-- statements
ROLLBACK TO name;
RELEASE SAVEPOINT name;

Examples:

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 50);
  SAVEPOINT sp1;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 999);
  ROLLBACK TO sp1;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
COMMIT;

Partial rollback within a transaction

Notes:

RELEASE SAVEPOINT removes the savepoint without rolling back. Useful for error recovery in complex transactions.

`CREATE FUNCTION`

Functions

Define a reusable function with PL/pgSQL or SQL

Syntax:

CREATE OR REPLACE FUNCTION func_name(args)
RETURNS return_type AS $$
BEGIN
  -- body
END;
$$ LANGUAGE plpgsql;

Examples:

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

Simple function returning a count

CREATE OR REPLACE FUNCTION apply_discount(
  p_product_id BIGINT,
  p_percent NUMERIC
) RETURNS NUMERIC AS $$
DECLARE
  new_price NUMERIC;
BEGIN
  UPDATE products
  SET price = price * (1 - p_percent / 100)
  WHERE id = p_product_id
  RETURNING price INTO new_price;
  RETURN new_price;
END;
$$ LANGUAGE plpgsql;

Function that modifies data and returns result

CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $$
  SELECT a + b;
$$ LANGUAGE sql;

Simple SQL-language function

Notes:

PL/pgSQL supports variables, loops, conditionals, and exception handling. SQL-language functions are simpler for pure queries.

`SERIAL / BIGSERIAL`

Sequences

Auto-incrementing integer columns

Syntax:

col_name SERIAL      -- 4-byte integer
col_name BIGSERIAL   -- 8-byte integer

Examples:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Auto-incrementing 4-byte id

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  type TEXT NOT NULL
);

Auto-incrementing 8-byte id for high-volume tables

Notes:

SERIAL is shorthand for creating a sequence and setting the default. Prefer BIGSERIAL for new tables to avoid overflow. Modern alternative: GENERATED ALWAYS AS IDENTITY.

`CREATE SEQUENCE / nextval`

Sequences

Create and use custom sequences

Syntax:

CREATE SEQUENCE seq_name START 1 INCREMENT 1;
SELECT nextval('seq_name');

Examples:

CREATE SEQUENCE invoice_number_seq START 1000 INCREMENT 1;

Custom sequence starting at 1000

SELECT nextval('invoice_number_seq');

Get next value from sequence

SELECT currval('invoice_number_seq');

Get current value (after nextval was called)

ALTER SEQUENCE invoice_number_seq RESTART WITH 5000;

Reset sequence to a new value

Notes:

Sequences are guaranteed to produce unique values even under concurrent access. They may have gaps after rollbacks.

`UUID`

Types

Universally unique identifier type

Syntax:

col_name UUID DEFAULT gen_random_uuid()

Examples:

CREATE TABLE sessions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Table with UUID primary key

SELECT gen_random_uuid();

Generate a random UUID

Notes:

gen_random_uuid() is built-in since PostgreSQL 13. For older versions, use the uuid-ossp extension.

`TIMESTAMPTZ`

Types

Timestamp with time zone -- the recommended timestamp type

Syntax:

col_name TIMESTAMPTZ DEFAULT NOW()

Examples:

SELECT NOW();

Current timestamp with time zone

SELECT NOW() AT TIME ZONE 'UTC';

Convert to UTC

SELECT created_at AT TIME ZONE 'America/New_York' FROM events;

Display in a specific time zone

SELECT * FROM events WHERE created_at > NOW() - INTERVAL '24 hours';

Events in the last 24 hours

Notes:

Always use TIMESTAMPTZ over TIMESTAMP. PostgreSQL stores TIMESTAMPTZ in UTC internally and converts on display.

`TEXT / VARCHAR / NUMERIC`

Types

Common PostgreSQL data types

Syntax:

TEXT              -- unlimited length string
VARCHAR(n)        -- string with max length
NUMERIC(p, s)     -- exact decimal number

Examples:

CREATE TABLE products (
  name TEXT NOT NULL,
  sku VARCHAR(50) UNIQUE,
  price NUMERIC(10, 2) NOT NULL
);

Common column types

SELECT CAST(price AS NUMERIC(10,2)) FROM products;

Cast to specific precision

Notes:

In PostgreSQL, TEXT and VARCHAR (without length) perform identically. Use NUMERIC for money and exact decimal values, not FLOAT or REAL.

`JSONB`

Types

Binary JSON type with indexing and operator support

Syntax:

col_name JSONB DEFAULT '{}'

Examples:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL DEFAULT '{}'
);

Table with JSONB column

CREATE INDEX idx_events_payload ON events USING gin (payload);

GIN index for fast JSONB queries

Notes:

JSONB is preferred over JSON for most use cases. It supports indexing, containment operators, and is faster for reads.

`EXPLAIN`

Performance

Show the query execution plan without running the query

Syntax:

EXPLAIN SELECT ...;

Examples:

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

Show execution plan

EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

JSON-formatted plan

Notes:

EXPLAIN shows the estimated plan without executing the query. Use EXPLAIN ANALYZE for actual timing.

`EXPLAIN ANALYZE`

Performance

Run the query and show actual execution times and row counts

Syntax:

EXPLAIN ANALYZE SELECT ...;

Examples:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

Actual execution time and plan

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 1;

Include buffer/cache usage stats

Notes:

EXPLAIN ANALYZE actually executes the query. Wrap in a transaction and ROLLBACK for write queries to avoid side effects.

`VACUUM / ANALYZE`

Performance

Reclaim storage and update query planner statistics

Syntax:

VACUUM [FULL] [ANALYZE] [table_name];

Examples:

VACUUM;

Reclaim dead tuple storage across all tables

VACUUM ANALYZE users;

Vacuum and update statistics for users table

VACUUM FULL large_table;

Reclaim maximum space (locks the table)

ANALYZE users;

Update planner statistics only

Notes:

Autovacuum handles this automatically in most cases. Run ANALYZE after bulk inserts or deletes to keep the query planner accurate.

`\l / \c`

psql CLI

List databases and connect to a database

Syntax:

\l              -- list databases
\c dbname       -- connect to database

Examples:

\l

List all databases

\c myapp_production

Connect to a specific database

\conninfo

Show current connection info

Notes:

psql meta-commands start with a backslash. They are not SQL and are only available in the psql shell.

`\dt / \d`

psql CLI

List tables and describe table structure

Syntax:

\dt             -- list tables
\d tablename    -- describe table

Examples:

\dt

List all tables in current schema

\dt public.*

List tables in the public schema

\d users

Show columns, types, and constraints for users table

\di

List all indexes

\dv

List all views

\df

List all functions

`\copy / \timing`

psql CLI

Import/export data and enable query timing

Syntax:

\copy table FROM 'file.csv' CSV HEADER;
\timing

Examples:

\copy users TO '/tmp/users.csv' CSV HEADER;

Export table to CSV

\copy users FROM '/tmp/users.csv' CSV HEADER;

Import CSV into table

\timing

Toggle query execution timing on/off

\x

Toggle expanded (vertical) display

\o /tmp/output.txt

Redirect query output to a file

Notes:

\copy runs on the client side (unlike COPY which runs on the server). Use \copy when you do not have server filesystem access.

PostgreSQL Best Practices

Query Optimization

Use EXPLAIN ANALYZE to profile slow queries
Create indexes on columns used in WHERE, JOIN, and ORDER BY
Use CONCURRENTLY when creating indexes in production
Prefer EXISTS over IN for correlated subqueries
Use partial indexes for filtered queries
Run ANALYZE after bulk data changes

Schema Design

Use TIMESTAMPTZ instead of TIMESTAMP
Prefer BIGSERIAL over SERIAL for primary keys
Use TEXT instead of VARCHAR unless you need a length limit
Use NUMERIC for money, never FLOAT
Add NOT NULL constraints wherever possible
Use ON CONFLICT for upsert instead of check-then-insert

Security

Always use parameterized queries to prevent SQL injection
Use ROLE and GRANT for least-privilege access
Enable SSL for all database connections
Use pg_hba.conf to restrict network access

Maintenance

Let autovacuum run -- do not disable it
Monitor table bloat and dead tuples
Set up regular pg_dump backups
Use pg_stat_statements to find slow queries