SQL Cheatsheet (PostgreSQL)

Syntax here is PostgreSQL-flavored; most SELECT/WHERE/JOIN statements are standard SQL and work unchanged on MySQL or SQLite, but functions like ILIKE, RETURNING, and the psql meta-commands are Postgres-specific.

psql meta-commands

\l

Lists all databases on the server.

\c mydb

Connects to a specific database.

\dt

Lists tables in the current database’s search path.

\d users

Describes a table: columns, types, indexes, and foreign keys.

\du

Lists database roles/users and their privileges.

\timing

Toggles showing query execution time after each statement - useful while tuning.

\x

Toggles expanded display, printing one column per line instead of a wide table - much easier to read for wide rows.

\q

Exits psql.

Querying

SELECT id, name, email FROM users;

Selects specific columns from a table.

SELECT * FROM users LIMIT 10;

Selects all columns, capped at 10 rows.

SELECT DISTINCT country FROM users;

Selects each unique value, removing duplicates.

SELECT * FROM users ORDER BY created_at DESC;

Sorts results, newest first.

SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 40;

Pagination: skips the first 40 rows, then returns the next 20.

Filtering

SELECT * FROM users WHERE country = 'IN';

Filters rows by an exact match.

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

Case-insensitive pattern match (Postgres-specific; use LIKE for case-sensitive, standard SQL).

SELECT * FROM orders WHERE status IN ('pending', 'processing');

Matches any value in a list.

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

Filters an inclusive date/number range.

SELECT * FROM users WHERE deleted_at IS NULL;

Filters for rows where a column has no value - = NULL never matches, IS NULL does.

SELECT * FROM users WHERE age > 18 AND country = 'IN';

Combines multiple conditions.

Joins

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

Inner join: returns only rows with a match in both tables.

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

Left join: returns every user, with NULL order columns for users who have none.

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Combines a left join with aggregation to count related rows per user, including zero.

Aggregation

SELECT COUNT(*) FROM users;

Counts all rows in a table.

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

Counts rows per group.

SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 100;

Filters groups after aggregation (WHERE can’t reference aggregate results; HAVING can).

SELECT AVG(amount), MIN(amount), MAX(amount), SUM(amount) FROM orders;

Common aggregate functions in one query.

SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

Aggregates by calendar month - a very common reporting pattern.

Inserting, updating, deleting

INSERT INTO users (name, email) VALUES ('Anupam', 'a@example.com');

Inserts a single row.

INSERT INTO users (name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com');

Inserts multiple rows in one statement.

INSERT INTO users (name, email) VALUES ('A', 'a@x.com')
RETURNING id;

Inserts a row and immediately returns a generated column (Postgres-specific) - avoids a follow-up SELECT.

UPDATE users SET status = 'active' WHERE id = 42;

Updates specific rows. Always include a WHERE clause unless you mean to update every row.

DELETE FROM users WHERE id = 42;

Deletes specific rows. Same warning applies.

INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

“Upsert”: inserts a row, or updates it if a unique/primary-key conflict occurs (Postgres-specific).

Schema (DDL)

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

Creates a table with an auto-incrementing primary key and sane defaults.

ALTER TABLE users ADD COLUMN age INT;

Adds a new column to an existing table.

ALTER TABLE users DROP COLUMN age;

Removes a column.

ALTER TABLE users RENAME COLUMN name TO full_name;

Renames a column.

DROP TABLE users;

Deletes a table and all its data - irreversible without a backup.

Indexes and performance

CREATE INDEX idx_users_email ON users (email);

Creates an index to speed up lookups/filters on a column.

CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

Creates an index that also enforces uniqueness.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@x.com';

Shows the query planner’s chosen execution plan and actual runtime - the standard first step when a query is slow.

DROP INDEX idx_users_email;

Removes an index.

Transactions

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

Groups multiple statements so they all succeed or all fail together (a transfer between two accounts).

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

Starts a transaction, then undoes it - useful for testing a destructive statement safely before committing.