PostgreSQL Cheat Sheet: The psql Meta-Commands and Postgres SQL I Actually Type

Most database tutorials stop at SELECT * FROM users. That is not where the time goes. The time goes into remembering whether it is \x or \timing that toggles expanded output, into writing an upsert that does not corrupt your invoice numbers, and into reading an EXPLAIN ANALYZE plan at 2am to figure out why a query that ran in 4ms last week now takes 11 seconds. This is a quick reference for the Postgres-specific things you reach for after the basics, organized the way I keep them in my head.

For the full searchable version with 80+ entries, pitfalls, and category filters, use the PostgreSQL Cheatsheet tool. This post walks through the ones I type most.

psql Meta-Commands: Navigate Without Leaving the Terminal

psql backslash commands are the fastest way to inspect a database. They are not SQL, so a semicolon is not required, and most accept a pattern argument.

\l              -- list all databases
\c shop         -- connect to the "shop" database
\dt             -- list tables in the current schema
\dt sales.*     -- list tables in the "sales" schema
\d orders       -- describe the orders table: columns, types, indexes, FKs
\d+ orders      -- same, plus storage and table size
\du             -- list roles and their attributes
\df *json*      -- list functions whose name contains "json"
\dn             -- list schemas
\x auto         -- expanded display: one column per line for wide rows
\timing on      -- print execution time after every query
\i seed.sql     -- run a SQL script from a file
\conninfo       -- show which host/db/user/port you are connected to
\copy orders TO 'out.csv' CSV HEADER   -- client-side export, no server file access
\! psql --version                      -- shell out without leaving psql

The two I forget under pressure are \conninfo (am I about to run this DELETE on staging or production?) and \x auto, which switches a 30-column row from an unreadable wrapped mess to a clean vertical key-value list. Turn on \timing on in your ~/.psqlrc and you will never wonder again.

generate_series: The Swiss Army Knife

generate_series builds a set of rows out of thin air. It is the cleanest way to make a calendar table, fill gaps in a time series, or generate test data without a loop.

-- A row per day for the whole of June
SELECT d::date
FROM generate_series('2026-06-01', '2026-06-30', interval '1 day') AS d;

-- Seed 1,000 fake orders
INSERT INTO orders (user_id, amount, created_at)
SELECT (random() * 500)::int + 1,
       round((random() * 200)::numeric, 2),
       now() - (random() * interval '90 days')
FROM generate_series(1, 1000);

Left-joining a report against generate_series is how you get zero-rows for days that had no sales, instead of those days silently vanishing from a chart.

JSONB: Query and Index Schemaless Data

JSONB is for genuinely schemaless blobs: third-party API payloads, per-user custom fields, event envelopes. It is queryable and indexable, so it is not a black box, but the operators trip people up.

-- -> returns JSON, ->> returns text
SELECT payload -> 'address' ->> 'city' AS city FROM events;

-- @> is containment: "does this row's payload contain this object?"
SELECT * FROM events WHERE payload @> '{"type": "signup"}';

-- ? checks for a top-level key
SELECT * FROM events WHERE payload ? 'utm_source';

-- jsonb_set updates one path without rewriting the whole document
UPDATE events SET payload = jsonb_set(payload, '{flags,beta}', 'true');

The indexing rule that saves the most time: a GIN index on the column (CREATE INDEX ON events USING GIN (payload)) accelerates @> containment, but it does not help an ->> 'city' = 'Berlin' equality filter. For that you need a functional index on the exact expression: CREATE INDEX ON events ((payload ->> 'city')). If you find yourself indexing the same key five times, that key wants to be a real column.

Indexes and EXPLAIN ANALYZE: Why Is This Slow?

Postgres has more index types than B-tree, and picking the right one is half the battle: B-tree for equality and ranges, GIN for JSONB and full-text and arrays, GiST for geometry and ranges, BRIN for huge append-only tables ordered by time.

When a query is slow, EXPLAIN ANALYZE is the first move, not the last. Read it bottom-up and hunt for Seq Scan on a large table.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at >= '2026-06-01' AND amount > 100;

If that prints Seq Scan on orders, the planner is reading every row. The usual culprits: the column is wrapped in a function (build a functional index), the literal type does not match the column type so the index is skipped, a leading-wildcard LIKE '%foo' that B-tree cannot serve (install pg_trgm and use a GIN index), or stale statistics (run ANALYZE orders). On a busy production table, build the index online so you do not block writes:

CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

Plain CREATE INDEX takes an ACCESS EXCLUSIVE lock and blocks every write for as long as the build runs, which on a 50M-row table is minutes of stalled checkouts. CONCURRENTLY builds in the background at the cost of two table scans. It cannot run inside a transaction block, which is the one gotcha.

Worked Scenario: A Safe Upsert with ON CONFLICT

Here is the case that comes up constantly. You have a daily_stats table keyed on (user_id, day), and a nightly job that recomputes today's row. You want "insert if new, update if it exists." The instinct is check-then-insert:

-- The naive, racy approach. DO NOT do this.
SELECT 1 FROM daily_stats WHERE user_id = 42 AND day = '2026-06-13';
-- if no row, INSERT; else UPDATE

The problem is the gap between the SELECT and the INSERT. Two concurrent jobs both see "no row," both try to insert, and one crashes on a unique-constraint violation, or worse, you wrapped it in logic that double-counts. You also pay two round trips for what should be one statement.

ON CONFLICT does it atomically in a single statement:

INSERT INTO daily_stats (user_id, day, events, revenue)
VALUES (42, '2026-06-13', 1200, 480.00)
ON CONFLICT (user_id, day)
DO UPDATE SET events  = EXCLUDED.events,
              revenue = EXCLUDED.revenue
RETURNING *;

EXCLUDED refers to the row you tried to insert, so you do not have to repeat the values. The whole thing is one atomic operation, so concurrent jobs serialize cleanly with no race and no application-side retry loop. Three things to know: the conflict target must be a UNIQUE or PRIMARY KEY constraint, not just a plain index; on a conflict the sequence is still advanced, so a serial column develops gaps (fine for surrogate keys, fatal for invoice numbers); and if the values would not change, ON CONFLICT (...) DO NOTHING is faster than DO UPDATE. That sequence-gap detail is exactly the kind of footnote that turns into a production incident if you only learn it from the manual after the fact.

A Few More I Keep Close

A couple of patterns round out the daily kit. Common table expressions make a multi-step query readable, and RECURSIVE walks a tree:

WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name FROM staff WHERE id = 1
  UNION ALL
  SELECT s.id, s.manager_id, s.name
  FROM staff s JOIN subordinates sub ON s.manager_id = sub.id
)
SELECT * FROM subordinates;

Window functions rank without collapsing rows: SELECT name, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) FROM sales gives each row its rank within its region while keeping every row visible. And RETURNING on an INSERT, UPDATE, or DELETE hands back the affected rows so you skip a follow-up SELECT.

I lean on this reference most during incidents, when I have no patience for a docs site with 40 tabs and a beginner walkthrough between me and the one escape sequence I need. If your SQL spans more than Postgres, the broader SQL Cheatsheet covers the cross-database syntax, and the Postgres tool stays entirely client-side, so it works fine behind a bastion host where you cannot install psql at all.

Keep the meta-commands close, reach for EXPLAIN ANALYZE before guessing, and let ON CONFLICT carry your upserts. Those three habits cover most of what separates a smooth Postgres day from a long one.

---

Made by Toolora · Updated 2026-06-13