psql commands
Meta-commands run inside psql (the PostgreSQL terminal client), not on the server. They start with a backslash and need no semicolon.
| What it does | Command |
|---|
| List databases | \l |
| Connect to another database | \c shop |
| List tables | \dt |
| List tables in every schema | \dt *.* |
| Describe a table (columns, indexes, keys) | \d users |
| Describe with storage and comments | \d+ users |
| List schemas | \dn |
| List views | \dv |
| List indexes | \di |
| List functions | \df |
| List roles (users) | \du |
| Show the current connection | \conninfo |
| Run SQL from a file | \i setup.sql |
| Toggle query timing | \timing |
| Expanded output (one column per line) | \x |
| Edit the last query in your editor | \e |
| Help on SQL syntax | \h CREATE TABLE |
| List all meta-commands | \? |
| Quit psql | \q |
Connecting from the terminal
| What it does | Command |
|---|
| Connect as a user to a database | psql -U postgres -d shop |
| Connect to a remote host and port | psql -h db.example.com -p 5432 -U app -d shop |
| Connect with a connection URL | psql postgresql://app:secret@localhost:5432/shop |
| Run one query and exit | psql -d shop -c "SELECT count(*) FROM users;" |
| Run a SQL file | psql -d shop -f setup.sql |
| Create a database from the shell | createdb shop |
| Drop a database from the shell | dropdb shop |
Databases and schemas
| What it does | SQL |
|---|
| Create a database | CREATE DATABASE shop; |
| Drop a database | DROP DATABASE shop; |
| Show the current database | SELECT current_database(); |
| Create a schema | CREATE SCHEMA sales; |
| Show the schema search order | SHOW search_path; |
| Look in a schema first | SET search_path TO sales, public; |
| Show the server version | SELECT version(); |
Tables and data types
| What it does | SQL |
|---|
| Create a table with an identity key | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| Add a column | ALTER TABLE users ADD COLUMN phone text; |
| Rename a column | ALTER TABLE users RENAME COLUMN country TO country_code; |
| Change a column's type | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| Set a default | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| Drop a column | ALTER TABLE users DROP COLUMN tags; |
| Empty a table and reset its identity | TRUNCATE orders, users RESTART IDENTITY; |
| Drop a table if it exists | DROP TABLE IF EXISTS products; |
| Exact decimal for money | numeric(10,2) |
| Date and time with time zone | timestamptz |
| Random UUID | SELECT gen_random_uuid(); |
| Enum type | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
Constraints and keys
| What it does | SQL |
|---|
| Add a primary key | ALTER TABLE logs ADD PRIMARY KEY (id); |
| Foreign key that deletes children | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| Add a check constraint | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| Unique where NULLs count as equal (15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| Make a column required | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| Drop a constraint | ALTER TABLE users DROP CONSTRAINT users_email_key; |
Standard SELECT, WHERE and JOIN are on the SQL cheat sheet; these are the PostgreSQL forms.
| What it does | SQL |
|---|
| Case-insensitive match | SELECT * FROM users WHERE name ILIKE 'a%'; |
| Regular expression match | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| First row per group | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| Limit and offset | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| Standard row limit | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| Cast with :: | SELECT '42'::integer + 1; |
| NULL fallback | SELECT name, COALESCE(email, 'none') FROM users; |
| Compare treating NULLs as equal | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| Generate a series of numbers | SELECT * FROM generate_series(1, 5); |
| Aggregate into a string | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| Conditional aggregate | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
Insert, update, delete and upsert
| What it does | SQL |
|---|
| Insert and get the new id back | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| Insert several rows | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert (insert or update) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| Insert, ignore duplicates | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| Update and see the changed rows | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| Update from another table | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| Delete and see what was deleted | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| Delete using another table | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| Copy rows into a new table | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| What it does | SQL |
|---|
| Get a field as JSON | SELECT profile -> 'plan' FROM users; |
| Get a field as text | SELECT profile ->> 'plan' FROM users; |
| Get a nested field as text | SELECT profile #>> '{address,city}' FROM users; |
| Rows whose JSON contains a value | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| Rows that have a key | SELECT * FROM users WHERE profile ? 'city'; |
| Set a key | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| Merge objects | UPDATE users SET profile = profile || '{"beta": true}'; |
| Remove a key | UPDATE users SET profile = profile - 'city'; |
| Build JSON from columns | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| Rows as a JSON array | SELECT jsonb_agg(users) FROM users; |
| Index for @> and ? lookups | CREATE INDEX users_profile_idx ON users USING gin (profile); |
Arrays
| What it does | SQL |
|---|
| Array literal | SELECT ARRAY[1, 2, 3]; |
| Rows where the array has a value | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| Rows where the array contains all of | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| One row per element | SELECT name, unnest(tags) FROM users; |
| Append an element | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| Array length | SELECT name, cardinality(tags) FROM users; |
Dates and times
| What it does | SQL |
|---|
| Now, with time zone | SELECT now(); |
| Today | SELECT current_date; |
| Add an interval | SELECT current_date + INTERVAL '7 days'; |
| Rows from the last 30 days | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| Truncate to the month | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| Extract a part | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| Difference between dates (days) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| Age between dates | SELECT name, age(current_date, hired_on) FROM employees; |
| Format a date as text | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| Parse text into a date | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| Convert to a time zone | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
Text functions
| What it does | SQL |
|---|
| Concatenate (NULL makes NULL) | SELECT name || ' <' || email || '>' FROM users; |
| Concatenate, skipping NULLs | SELECT concat_ws(' ', name, email) FROM users; |
| Upper and lower case | SELECT upper(name), lower(name) FROM users; |
| Length | SELECT name, length(name) FROM users; |
| Part of a string | SELECT substring(name FROM 1 FOR 2) FROM users; |
| Replace | SELECT replace(email, '@example.com', '') FROM users; |
| Split a part | SELECT split_part(email, '@', 2) FROM users; |
| Regular expression replace | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| Trim spaces | SELECT trim(' hi '); |
CTEs and window functions
| What it does | SQL |
|---|
| Name a subquery with WITH | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| Recursive CTE (count to 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| Number rows per group | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| Rank with ties | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| Running total | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| Previous row's value | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| Join to each row's own subquery | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
Indexes
| What it does | SQL |
|---|
| B-tree index (the default) | CREATE INDEX orders_user_idx ON orders (user_id); |
| Unique index | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| Multicolumn index | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| Partial index | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| Expression index | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| Covering index (index-only scans) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| Build without blocking writes | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| Drop an index | DROP INDEX IF EXISTS orders_user_idx; |
| See the plan | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| See the plan with real timings | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
Transactions and locks
| What it does | SQL |
|---|
| Start, then keep or undo | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| Undo part of a transaction | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| Stricter isolation | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| Lock the rows you read | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| Job queue: skip locked rows | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
Users, roles and privileges
| What it does | SQL |
|---|
| Create a login role | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| Read-only access to a table | GRANT SELECT ON users TO app; |
| Read access to every table in a schema | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| Take a privilege back | REVOKE SELECT ON users FROM app; |
| Change a password | ALTER ROLE app PASSWORD 'new-secret'; |
| Show the current user | SELECT current_user; |
Maintenance and monitoring
| What it does | SQL |
|---|
| Running queries | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| Cancel a query | SELECT pg_cancel_backend(12345); |
| End a connection | SELECT pg_terminate_backend(12345); |
| Size of a table with its indexes | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| Size of the database | SELECT pg_size_pretty(pg_database_size(current_database())); |
| Update planner statistics | ANALYZE orders; |
| Reclaim dead rows | VACUUM orders; |
| Rewrite a table to its minimum size (locks it) | VACUUM FULL orders; |
| Show a setting | SHOW work_mem; |
| Change a setting for this session | SET work_mem = '64MB'; |
Backup and restore
Shell commands from the PostgreSQL client tools.
| What it does | Command |
|---|
| Dump a database to a SQL file | pg_dump -U postgres shop > shop.sql |
| Dump in the custom format | pg_dump -Fc -U postgres shop > shop.dump |
| Dump one table | pg_dump -t orders shop > orders.sql |
| Restore a SQL dump | psql -d shop -f shop.sql |
| Restore a custom-format dump | pg_restore -d shop shop.dump |
| Dump every database and role | pg_dumpall > cluster.sql |
| Export a query to CSV (from psql) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| Import a CSV (from psql) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
Every psql command and PostgreSQL statement you reach for, in copy-paste tables: connecting and the \d commands first, then tables, upserts, JSONB, dates, window functions, indexes, roles, backups and maintenance.
The SQL here is PostgreSQL 17 syntax, and most of it works in earlier versions too. The SQL statements were checked on PostgreSQL 17; to try one, paste it into the PostgreSQL playground, which comes with sample tables.