Free PostgreSQL Commands Cheat Sheet Online — 120+ psql & SQL Commands Reference

**PostgreSQL is the world's most advanced open-source relational database**, trusted by startups and Fortune 500 companies alike. But mastering its commands — from psql meta-commands and DDL to window functions, indexes, and concurrency control — takes practice. We built this **free PostgreSQL Commands Cheat Sheet** with 120 entries across 10 categories, so you can find the right SQL syntax instantly. Search by keyword, filter by category, and copy commands to your clipboard with one click. No signup. No tracking. Open PostgreSQL Commands Cheat Sheet → ## 1. psql Meta-Commands (12 entries) The `psql` interactive terminal has its own set of backslash-prefixed meta-commands that are distinct from SQL. These are the commands you use to navigate databases, inspect schemas, and manage your PostgreSQL environment: - **`\l`** — List all databases with owner, encoding, and access privileges - **`\c dbname`** — Connect to a different database - **`\dt`** — List tables in the current schema (`\dt *.` for all schemas) - **`\d tablename`** — Describe a table: columns, types, indexes, constraints - **`\di`** / **`\dv`** / **`\df`** / **`\dn`** — List indexes, views, functions, schemas - **`\du`** — List database roles (users) - **`\dp`** — Display table access privileges (GRANT/REVOKE) - **`\timing`** — Toggle query execution time display (essential for performance work) - **`\x`** — Toggle expanded output (vertical column display for wide tables) Pro tip: Add `+` to any describe command for extended output. For example, `\dt+` shows table sizes, `\d+ users` shows storage stats and comments, `\df+ get_report` shows the function source code. ## 2. Database & Schema DDL (12 entries) Data Definition Language (DDL) creates and modifies the structure of your database. These commands define schemas, tables, indexes, views, sequences, and constraints: - **`CREATE DATABASE`** — Create a new database with owner, encoding, and locale - **`DROP DATABASE`** — Permanently delete a database (use `IF EXISTS`) - **`CREATE TABLE`** — Define columns, types, constraints (PRIMARY KEY, UNIQUE, CHECK, DEFAULT) - **`ALTER TABLE`** — Add/drop/rename columns, add/drop constraints, change types - **`DROP TABLE`** — Delete a table (use `CASCADE` to also drop dependent views/FKs) - **`CREATE SCHEMA`** — Namespace for organizing database objects - **`CREATE INDEX`** — Build a B-tree index (add `CONCURRENTLY` for zero-downtime) - **`CREATE VIEW`** / **`CREATE MATERIALIZED VIEW`** — Virtual tables; materialized views cache results - **`TRUNCATE TABLE`** — Fast delete all rows (minimal WAL, much faster than DELETE) - **`COMMENT ON`** — Add documentation strings to any database object PostgreSQL's DDL is transactional: you can wrap multiple `CREATE TABLE`/`ALTER TABLE` statements in a `BEGIN...COMMIT` block, and if any fail, they all roll back. This is unique among major databases. ## 3. Querying & Filtering (12 entries) The core of SQL — retrieving and filtering data efficiently: - **`SELECT ... FROM ... WHERE`** — The fundamental query pattern - **`ORDER BY`** — Sort results (ASC/DESC, NULLS FIRST/LAST) - **`LIMIT / OFFSET`** — Pagination (for large offsets, prefer keyset pagination) - **`DISTINCT`** / **`DISTINCT ON`** — Remove duplicates; DISTINCT ON keeps first per group - **`BETWEEN`** — Range check (inclusive): `WHERE total BETWEEN 50 AND 100` - **`IN / NOT IN`** — Match against a list or subquery result - **`LIKE / ILIKE`** — Pattern matching (`%` any chars, `_` single char; ILIKE is case-insensitive) - **`IS NULL / IS NOT NULL`** — NULL testing (never use `= NULL`) - **`CASE WHEN THEN ELSE END`** — Conditional expressions in SELECT, WHERE, ORDER BY - **`COALESCE`** / **`NULLIF`** — Handle NULLs gracefully; NULLIF prevents division-by-zero PostgreSQL's `DISTINCT ON` is non-standard and exceptionally useful: `SELECT DISTINCT ON (user_id) user_id, total FROM orders ORDER BY user_id, created_at DESC` returns the most recent order per user in a single query. ## 4. JOINs & Subqueries (12 entries) Combining data from multiple tables is where relational databases shine. PostgreSQL supports every standard JOIN type plus powerful extensions: - **`INNER JOIN`** — Return rows only when matches exist in both tables - **`LEFT JOIN`** / **`RIGHT JOIN`** — All rows from one table + matching rows from the other - **`FULL OUTER JOIN`** — All rows from both tables, NULLs where no match - **`CROSS JOIN`** — Cartesian product (every row paired with every row) - **`LATERAL JOIN`** — Subquery can reference columns from preceding FROM items (like a for-each loop) - **`EXISTS / NOT EXISTS`** — Check whether a correlated subquery returns any rows - **`ANY / ALL`** — Compare against all subquery results - **`UNION / INTERSECT / EXCEPT`** — Combine, intersect, or subtract result sets - **`WITH` (CTE)** — Named, reusable subquery blocks for readability - **`WITH RECURSIVE`** — Self-referencing CTE for trees, graphs, and hierarchies `LATERAL` is PostgreSQL's secret weapon. Think of it like a for-each loop in SQL: the lateral subquery executes once per outer row and can reference outer columns. Perfect for "top N per group" queries without window functions. ## 5. Aggregation & Window Functions (12 entries) Window functions let you perform calculations across sets of rows without collapsing them with GROUP BY. They're one of PostgreSQL's most powerful features: - **`COUNT / SUM / AVG / MIN / MAX`** — Basic aggregates, with optional `FILTER (WHERE ...)` clause - **`GROUP BY / HAVING`** — Group rows, then filter groups (HAVING filters after aggregation) - **`string_agg`** / **`array_agg`** — Concatenate or collect values into arrays per group - **`ROW_NUMBER()`** — Sequential integers within each partition - **`RANK()`** / **`DENSE_RANK()`** — Rank with gaps (1,1,3) or without gaps (1,1,2) - **`LAG()`** / **`LEAD()`** — Access previous or following row values - **`FIRST_VALUE()`** / **`LAST_VALUE()`** / **`NTH_VALUE()`** — Boundary values in the window frame - **`NTILE(n)`** — Divide into n buckets (quartiles, deciles, percentiles) - **`PARTITION BY + ROWS/RANGE frame`** — Define the window and its boundary Window functions eliminate the need for complex self-joins and correlated subqueries for running totals, moving averages, and rank calculations. They're also usually faster — the optimizer can compute them in a single pass over the data. ## 6. Data Types & Functions (12 entries) PostgreSQL's rich type system and built-in functions reduce application code complexity: - **Numeric types**: `integer`, `bigint`, `serial/bigserial` (auto-increment), `numeric(p,s)` for exact decimal - **Character types**: `text` (unlimited, preferred), `varchar(n)`, `char(n)` (fixed-width) - **`boolean`** — True/false with `bool_and()`/`bool_or()` aggregates - **Date/Time**: `date`, `timestamp`, `timestamptz` (UTC stored, session timezone displayed), `interval` - **`json` / `jsonb`** — json stores exact text; jsonb is binary, indexable, and faster for queries - **`array`** — Multi-value columns with `ANY()`, `unnest()`, and GIN indexing - **`enum`** — Custom fixed-value set; more type-safe than text with CHECK - **`uuid`** — 128-bit identifier via `gen_random_uuid()`; collision-resistant for distributed systems - **`generate_series()`** — Generate sequential data for test fixtures and calendar tables - **String functions**: `upper/lower`, `split_part`, `regexp_replace`, `concat_ws`, `format` - **Date functions**: `date_trunc`, `extract`, `age`, `date_part`, timezone conversion - **Type casting**: `::type` suffix or `CAST(expr AS type)` PostgreSQL's `jsonb` type deserves special mention: it supports GIN indexing with operators like `@>` (contains), `->>` (extract as text), and `#>>` (extract at path), making PostgreSQL a competitive document store alongside its relational capabilities. ## 7. Indexes & Performance (12 entries) The right index can turn a 30-second sequential scan into a sub-millisecond lookup. PostgreSQL offers more index types than any other open-source database: - **B-tree (default)** — For `=`, `<`, `>`, `BETWEEN`, `IN`, `IS NULL`, `LIKE 'prefix%'` - **`CREATE UNIQUE INDEX`** — Uniqueness constraint via index (NULLs are distinct) - **`CREATE INDEX CONCURRENTLY`** — Build without write-locking the table (zero downtime) - **Partial indexes** — Index only a subset with `WHERE` (e.g., active users only) - **Expression indexes** — Index computed values: `lower(email)`, `(details->>'category')` - **Multicolumn indexes** — Leading columns can be used independently; column order matters - **GIN (Generalized Inverted Index)** — For arrays, jsonb, full-text search (tsvector) - **GiST (Generalized Search Tree)** — For geometric data, KNN distance queries, range types - **BRIN (Block Range INdex)** — Tiny index for naturally ordered large tables; 1000x smaller than B-tree - **`EXPLAIN`** / **`EXPLAIN ANALYZE`** — View query plan and actual execution statistics - **`pg_stat_user_indexes`** — Identify unused indexes wasting disk space and write performance The `EXPLAIN (ANALYZE, BUFFERS)` command is your most important performance tool. Compare the estimated `rows=` with actual `rows=` — large discrepancies mean outdated statistics (run `ANALYZE`). Check `Buffers: shared hit=` vs `read=` to find cache misses. ## 8. Transactions & Concurrency (12 entries) PostgreSQL's MVCC (Multi-Version Concurrency Control) is what makes it robust under concurrent workloads: - **`BEGIN / COMMIT / ROLLBACK`** — Atomic transaction blocks (all or nothing) - **`SAVEPOINT`** / **`ROLLBACK TO SAVEPOINT`** — Partial rollback within a transaction - **Isolation levels**: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE - **`SELECT FOR UPDATE`** / **`FOR SHARE`** — Row-level locking for safe counter updates - **`LOCK TABLE`** — Explicit table-level lock in various modes - **`NOWAIT` / `SKIP LOCKED`** — Non-blocking locks; SKIP LOCKED enables job queues - **Advisory locks** — Application-defined locks via `pg_advisory_lock()` for distributed coordination - **`VACUUM`** / **`VACUUM FULL`** — Reclaim dead tuple space; FULL rewrites the table - **`ANALYZE`** — Update statistics for the query planner - **`REINDEX`** — Rebuild bloated indexes (use `CONCURRENTLY` to avoid write locks) `SKIP LOCKED` is the key to building a reliable PostgreSQL job queue: `SELECT * FROM tasks WHERE status = 'pending' ORDER BY created_at LIMIT 5 FOR UPDATE SKIP LOCKED`. Multiple workers pick up different tasks with zero blocking and zero deadlocks. ## 9. User & Permissions (12 entries) PostgreSQL's role-based access control with Row-Level Security enables fine-grained, multi-tenant authorization: - **`CREATE ROLE`** / **`CREATE USER`** — LOGIN for users, NOLOGIN for group roles - **`GRANT`** / **`REVOKE`** — Table-level: SELECT, INSERT, UPDATE, DELETE; Sequence: USAGE; Function: EXECUTE - **`ALTER DEFAULT PRIVILEGES`** — Auto-grant on future objects created by a role - **Row-Level Security (RLS)** — Per-row access control with `ENABLE ROW LEVEL SECURITY` - **`CREATE POLICY`** — USING (visibility) and WITH CHECK (validation) expressions - **Database/Schema grants** — CONNECT on database, USAGE on schema (required before table access) - **`pg_roles`** / **`pg_authid`** — Inspect roles and memberships - **`SET ROLE`** — Execute commands with another role's privileges - **`REASSIGN OWNED`** / **`DROP OWNED`** — Transfer or drop all objects before dropping a role RLS policies stack with OR logic: if *any* policy grants access, the row is visible. Set application user ID via `current_setting('app.current_user_id')` in your policies, and configure it per-session with `SET app.current_user_id = '42'` at connection time. ## 10. Backup, Restore & Administration (12 entries) Production PostgreSQL requires a solid backup strategy and monitoring: - **`pg_dump`** — Single-database export; `-Fc` for compressed custom format (parallel restore) - **`pg_dumpall`** — Cluster-wide export including roles and tablespaces - **`pg_restore`** — Restore from archive; `-j 8` for 8-way parallel restore - **`psql -f`** — Execute SQL scripts; `-1` for single-transaction execution - **`COPY`** — Bulk CSV import/export; `\copy` for client-side file access - **`pg_stat_activity`** — All current connections and running queries - **`pg_locks`** — All held and waiting locks for deadlock debugging - **`pg_stat_database`** — Per-database transaction counts, cache hit ratio, tuple statistics - **`CHECKPOINT`** — Force write of all dirty shared buffers to disk - **`ALTER SYSTEM SET`** — Change server parameters in postgresql.auto.conf - **`SHOW`** / **`current_setting()`** — Inspect runtime configuration values - **`pg_terminate_backend()`** / **`pg_cancel_backend()`** — Kill stuck queries or idle connections For production critical databases, combine `pg_dump` nightly backups with WAL archiving (`archive_mode = on`, `archive_command`) and `pg_basebackup` for point-in-time recovery (PITR). This lets you restore to any point in time, not just the last backup. ## Production PostgreSQL Workflow Example Here's a real-world PostgreSQL pattern combining multiple commands from this cheat sheet — setting up a multi-tenant SaaS schema with RLS: ```sql -- 1. Create schema and roles CREATE SCHEMA saas; CREATE ROLE app_user WITH LOGIN PASSWORD 's3cret'; CREATE ROLE app_owner WITH LOGIN PASSWORD 'admin_s3cret'; GRANT CONNECT ON DATABASE myapp TO app_user, app_owner; GRANT USAGE ON SCHEMA saas TO app_user; GRANT ALL PRIVILEGES ON SCHEMA saas TO app_owner; -- 2. Create tenant-aware table with RLS CREATE TABLE saas.documents ( id BIGSERIAL PRIMARY KEY, tenant_id INTEGER NOT NULL, title TEXT NOT NULL, body TEXT, created_at TIMESTAMPTZ DEFAULT now() ); CREATE INDEX idx_docs_tenant ON saas.documents (tenant_id, created_at DESC); -- 3. Enable RLS and create isolation policy ALTER TABLE saas.documents ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON saas.documents FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::integer); -- 4. Session setup in application code -- SET app.current_tenant_id = '42'; -- Now all queries on documents are automatically scoped to tenant 42 -- 5. Performance monitoring -- Check if the RLS policy is using indexes: EXPLAIN ANALYZE SELECT * FROM saas.documents WHERE title ILIKE '%report%'; -- Check for slow queries: SELECT pid, query, state, wait_event_type, wait_event, query_start FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '1 minute'; ``` This pattern combines DDL, indexing, RLS policies, session configuration, and performance monitoring — all from different categories in the cheat sheet. ## Related DevToolkit Resources If you're working with PostgreSQL, these related tools and cheat sheets will help: - **[SQL Advanced Patterns Cheat Sheet](/sql-advanced-patterns-cheat-sheet/)** — Window functions, CTEs, recursive queries, and advanced SQL patterns - **[Docker Commands Expansion Cheat Sheet](/docker-commands-expansion-cheat-sheet/)** — Running PostgreSQL in Docker containers for development - **[DevOps Commands Cheat Sheet](/devops-commands-cheat-sheet/)** — Infrastructure commands for managing database servers - **[Kubernetes Commands Cheat Sheet](/kubernetes-commands-cheat-sheet/)** — Running PostgreSQL on Kubernetes with StatefulSets and persistent volumes Open the PostgreSQL Commands Cheat Sheet →