glunty

PostgreSQL Cheat Sheet

A searchable reference of PostgreSQL commands, types, and JSONB operators with plain-English notes.

Syntax What it does
\l List all databases on the server.
\c dbname Connect to a different database by name.
\dt List tables in the current schema.
\d tablename Describe a table: its columns, types, indexes, and constraints.
\du List roles (users) and their attributes.
\dn List the schemas in the current database.
\df List functions.
\di List indexes.
\x Toggle expanded display, showing wide rows as vertical key and value pairs.
\timing Toggle reporting of how long each query takes to run.
\i file.sql Run the SQL commands stored in a file.
\copy Copy data between a table and a client-side file over the psql connection.
\q Quit the psql session.
\? Show help for the psql backslash meta-commands.
SELECT * FROM t LIMIT 10 Return at most 10 rows from the result.
SELECT * FROM t LIMIT 10 OFFSET 20 Skip the first 20 rows then return the next 10 (basic paging).
SELECT DISTINCT ON (col) * FROM t ORDER BY col Keep only the first row for each distinct value of col.
WHERE name ILIKE '%joe%' Match a pattern without regard to letter case.
INSERT INTO t (a) VALUES (1) RETURNING id Return columns from the rows just inserted, updated, or deleted.
generate_series(1, 10) Produce a set of rows counting from 1 to 10.
COALESCE(col, 0) Return the first argument that is not null.
string_agg(name, ', ') Concatenate values from many rows into one delimited string.
WITH cte AS (SELECT ...) SELECT * FROM cte Define a named subquery (common table expression) used by the main query.
INSERT INTO t (id) VALUES (1) ON CONFLICT (id) DO UPDATE SET ... Upsert: insert a row, or update it when the key already exists.
NULLIF(a, b) Return null when the two arguments are equal, otherwise the first.
SELECT count(*) FROM t GROUP BY col Count the rows in each group.
SERIAL Auto-incrementing 4-byte integer, a legacy shorthand backed by a sequence.
BIGSERIAL Auto-incrementing 8-byte integer for tables that outgrow SERIAL.
UUID 128-bit universally unique identifier, often filled with gen_random_uuid().
JSONB Binary JSON stored in a decomposed, indexable form.
TEXT Variable-length string with no length limit.
VARCHAR(n) Variable-length string capped at n characters.
TIMESTAMPTZ Timestamp stored in UTC and shown in the session time zone.
BOOLEAN True or false value, and it also allows null.
int[] Array column, here an array of integers.
NUMERIC(10, 2) Exact decimal with 10 total digits and 2 after the point (good for money).
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy') Define an enumerated type with a fixed, ordered set of allowed labels.
data->'items' Get an object field or array element as jsonb, keeping JSON typing.
data->>'name' Get an object field or array element as plain text.
data #> '{a,b}' Get the value at the given path (an array of keys) as jsonb.
data @> '{"a":1}' Return true when the left jsonb contains the right jsonb.
data ? 'key' Return true when the string exists as a top-level key.
jsonb_build_object('a', 1) Build a jsonb object from alternating key and value arguments.
jsonb_array_elements(data) Expand a JSON array into one row per element.
jsonb_set(data, '{a}', '5') Return the jsonb with the value at the given path replaced.
jsonb_typeof(data) Return the type of the top-level JSON value as text.
data - 'key' Return the object with the given key removed.
CREATE DATABASE app Create a new database.
CREATE ROLE app LOGIN PASSWORD 'secret' Create a login role (user) with a password.
GRANT SELECT ON t TO app Give a role permission to read a table.
CREATE INDEX idx ON t (col) Build an index on a column to speed up lookups.
CREATE INDEX CONCURRENTLY idx ON t (col) Build an index without locking writes, which is safe on a live table.
EXPLAIN ANALYZE SELECT ... Run a query and print its real execution plan with row counts and timings.
VACUUM Reclaim storage from dead rows left behind by updates and deletes.
VACUUM ANALYZE Vacuum and refresh planner statistics in one pass.
pg_dump dbname > out.sql Export a database to a SQL script (run in the shell, not inside psql).
ALTER TABLE t ADD COLUMN c text Change a table, here by adding a new column.

Runs entirely in your browser. Nothing you type is sent anywhere; open DevTools and watch the Network tab to verify zero requests.

What this tool does

This is a searchable quick reference for everyday PostgreSQL syntax: psql meta-commands, common query patterns, column types, JSONB operators, and administration commands. Each row pairs the exact syntax with a plain-English note on what it does. Type in the filter box to search across both columns, or tap a category button to focus on one area. The whole list is built into the page, so it works offline and sends nothing anywhere.

How to use it

Start typing in the Filter box. Entering jsonb surfaces the JSON operators; entering index shows the ways to build one; entering vacuum jumps to the cleanup commands. The category buttons (psql commands, Query, Types, JSONB, Admin) narrow the table to one area and combine with the text filter, so you can pick JSONB and type contains to zero in on the containment operator. Clear the box to see the full sheet again.

Common use cases

  • Recalling a psql meta-command like \dt or \d tablename before describing a table.
  • Copying the right JSONB operator, such as -> versus ->>, into a query.
  • Checking which column type to use, like TIMESTAMPTZ for time or NUMERIC for money.
  • Looking up the safe way to add an index on a live table with CREATE INDEX CONCURRENTLY.
  • Reviewing core PostgreSQL syntax before an interview or a new project.

Common pitfalls

  • Confusing -> with ->>. The -> operator returns jsonb while ->> returns text, so comparing the jsonb form to a plain string can silently match nothing. Use ->> when you want text.
  • Reaching for SERIAL by habit. SERIAL still works, but the SQL-standard GENERATED ALWAYS AS IDENTITY is now preferred because it handles column ownership and permissions more cleanly.
  • Building an index the blocking way. A plain CREATE INDEX locks writes on a busy table until it finishes. On production use CREATE INDEX CONCURRENTLY, which does not block writes.
  • Ignoring dead rows. Updates and deletes leave dead tuples that bloat a table. Autovacuum usually handles this, but heavy write loads may need a manual VACUUM ANALYZE to reclaim space and refresh statistics.

Frequently asked questions

How do I list tables in psql?
Connect to your database with psql and run the \dt meta-command to list the tables in the current schema. Add a pattern such as \dt public.* to filter by schema, or use \dt+ to include sizes and descriptions, and \dtS to include system tables. If you need the list inside a normal SQL query instead, select from the information_schema.tables view.
What is the difference between the -> and ->> operators in PostgreSQL?
Both pull a value out of a json or jsonb column, but they return different types. The -> operator returns the value as jsonb, so it keeps JSON typing and can be chained to reach deeper into the structure. The ->> operator returns the value as plain text, which is what you usually want for display or for comparing against a string. A frequent bug is comparing a -> result (jsonb) to a text literal and getting no matches; switch to ->> when you need text.
What is JSONB and how does it differ from JSON in PostgreSQL?
JSONB stores JSON in a decomposed binary form rather than as raw text. Because it is pre-parsed, JSONB is faster to query, can be indexed with GIN, and supports operators like @> for containment and ? for key existence. The trade-offs are slightly slower inserts and that it does not preserve key order or duplicate keys. The plain json type keeps the exact input text, so pick json only when you must retain the original formatting; otherwise JSONB is the usual default.
What is the difference between SERIAL and a generated identity column?
SERIAL is an older shorthand that creates an integer column backed by a sequence and sets a default of nextval on it. GENERATED ALWAYS AS IDENTITY is the SQL-standard replacement added in PostgreSQL 10; it ties the sequence to the column more cleanly, manages ownership and permissions automatically, and blocks accidental manual inserts unless you write OVERRIDING SYSTEM VALUE. For new tables an identity column is generally preferred, while SERIAL remains common in older schemas.
How do I see the query plan for a PostgreSQL query?
Prefix the statement with EXPLAIN to see the planner estimate without running it, or EXPLAIN ANALYZE to actually run the query and report real row counts and timings. Add options like EXPLAIN (ANALYZE, BUFFERS) to include cache and disk activity. Read the plan tree from the innermost node outward, and watch for sequential scans on large tables or big gaps between estimated and actual rows, which often point to a missing index or stale statistics.
Does this PostgreSQL cheat sheet send my searches anywhere?
No. The entire syntax list is baked into the page, and every search and category filter runs locally in your browser with JavaScript, so nothing you type is sent to any server. There is no database connection and no PostgreSQL server involved; it is a static reference. Open your browser DevTools and watch the Network tab while you search to confirm there are zero requests.

Embed this tool

Free for any use; attribution appreciated. Paste this on your site:

The embed runs the same tool that lives at this URL. No tracking; no ads inside the embed. Resize height as needed for your layout.

Cite this tool

For academic, journalistic, or technical references. Pick a format:

Citations use 2026 as the publication year. Access date is left as a fillable placeholder where the citation style expects one.

Embedded tool from glunty.com