PostgreSQL Cheat Sheet

Updated 2026-07-16

🎯Take a practice test on this topic10 practice tests · 381 questions→

🧠Study flashcards on this topic212 cards · spaced repetition→

PostgreSQL (version 18, released September 2025) is an advanced open-source relational database management system known for its robustness, extensibility, and SQL standards compliance. Originally developed at UC Berkeley in the 1980s, it supports complex queries, ACID transactions, and a wide array of data types including JSON, arrays, geospatial data, and vectors. PostgreSQL 18 introduces asynchronous I/O, UUIDv7, virtual generated columns, and temporal constraints — continuing its position as the most feature-rich open-source database. The key to mastering PostgreSQL lies in understanding its query planner — learning to read EXPLAIN ANALYZE output transforms guesswork into precision when optimizing performance.

What This Cheat Sheet Covers

This topic spans 30 focused tables and 296 indexed concepts, 212 flashcards, 10 practice tests with 381 questions. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Core Data TypesTable 2: Querying — SELECT and FilteringTable 3: JoinsTable 4: Aggregate FunctionsTable 5: Window FunctionsTable 6: Common Table Expressions (CTEs)Table 7: Data ModificationTable 8: Schema and DDL OperationsTable 9: ConstraintsTable 10: IndexesTable 11: Transactions and ConcurrencyTable 12: JSON OperationsTable 13: String FunctionsTable 14: Date and Time FunctionsTable 15: Conditional ExpressionsTable 16: SubqueriesTable 17: Functions and ProceduresTable 18: TriggersTable 19: Views and Materialized ViewsTable 20: PartitioningTable 21: Full-Text SearchTable 22: ExtensionsTable 23: Administration and MaintenanceTable 24: Security and RolesTable 25: Performance TuningTable 26: Connection ManagementTable 27: ReplicationTable 28: Foreign Data Wrappers (FDW)Table 29: psql Meta-CommandsTable 30: Advanced Features

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Core Data Types

Choosing the right column type is the first design decision in any schema, and PostgreSQL gives you a richer palette than most databases — exact decimals for money, native UUIDs, JSONB for semi-structured data, arrays, and ranges all sit alongside the usual integers and strings. The notes here flag the modern defaults that experienced users reach for: TIMESTAMPTZ over TIMESTAMP, JSONB over JSON, IDENTITY over the legacy SERIAL.

Type	Example	Description
INTEGER	`user_id INTEGER`	• 4-byte signed integer storing values from -2,147,483,648 to 2,147,483,647 • most common choice for IDs and counters.
BIGINT	`transaction_id BIGINT`	• 8-byte signed integer for very large numbers • essential for high-volume systems where INTEGER would overflow.
SMALLINT	`age SMALLINT`	2-byte signed integer (-32,768 to 32,767) — use for small-range values to save storage.
NUMERIC(p,s)	`price NUMERIC(10,2)`	• Exact arbitrary-precision decimal • use for financial calculations where floating-point errors are unacceptable.
REAL	`measurement REAL`	• 4-byte floating-point with ~6 decimal digits precision • faster than NUMERIC but inexact.
DOUBLE PRECISION	`latitude DOUBLE PRECISION`	• 8-byte floating-point with ~15 decimal digits precision • standard for scientific and geospatial calculations.
TEXT	`description TEXT`	• Unlimited-length string • identical performance to VARCHAR but without length limit — preferred unless you need length validation.
VARCHAR(n)	`name VARCHAR(100)`	• Variable-length string with optional maximum length • no performance advantage over TEXT — length is only a constraint.
CHAR(n)	`country_code CHAR(2)`	• Fixed-length string padded with spaces • rarely needed — only use for truly fixed-width data like codes.
BOOLEAN	`is_active BOOLEAN`	• Stores TRUE, FALSE, or NULL • accepts various input formats like `'t'`, `'yes'`, `'1'` for TRUE.
DATE	`birth_date DATE`	Calendar date without time — range from 4713 BC to 5874897 AD.
TIMESTAMP	`created_at TIMESTAMP`	• Date and time without timezone • stores the date and time exactly as written, with no timezone context, so the same value can mean different real moments depending on which zone it's read in.
TIMESTAMPTZ	`updated_at TIMESTAMPTZ`	• Date and time with timezone • converts to UTC internally — always prefer this over TIMESTAMP for consistency.
INTERVAL	`duration INTERVAL`	• Time span like `'3 days'` or `'2 hours 30 minutes'` • supports arithmetic with dates and timestamps.
UUID	`session_id UUID DEFAULT gen_random_uuid()`	• 128-bit universally unique identifier • use `uuidv7()` (PG18) for time-sortable IDs or `gen_random_uuid()` for random UUIDv4.
JSONB	`config JSONB`	• Binary JSON with full indexing support • slightly slower to insert but much faster to query — the standard choice over JSON.
JSON	`metadata JSON`	• Stores JSON as text, validates syntax • doesn't index efficiently — use JSONB instead.
ARRAY	`tags TEXT[]`	• Variable-length multidimensional array of any type • use bracket notation `[1]` for access.
BYTEA	`file_data BYTEA`	Binary data storage for images and files — stored as byte array with hex or escape encoding.
GENERATED ALWAYS AS IDENTITY	`id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY`	• SQL-standard auto-incrementing column • replaces SERIAL — sequence is owned by the column and cannot be accidentally overridden.
daterange / tstzrange	`valid_period tstzrange`	• Built-in range types for dates and timestamps • support overlap, contains, adjacent operators — essential for temporal data.
SERIAL (legacy)	`id SERIAL PRIMARY KEY`	• Auto-incrementing INTEGER via implicit sequence • use IDENTITY columns instead for new tables — SERIAL has ownership issues.

Table 2: Querying — SELECT and Filtering

Everything you do in PostgreSQL eventually flows through a SELECT, so these are the clauses you'll type most often — picking columns, narrowing rows with WHERE, sorting, and paging results. Pay attention to the PostgreSQL-specific tools like DISTINCT ON and ILIKE, and the practical warnings (large OFFSETs and NULL = NULL trip people up constantly).

Technique	Example	Description
SELECT columns	`SELECT id, name FROM users`	• Retrieves specified columns from a table • use `` for all columns but explicit column lists* are preferred.

The rest of this section is part of the full cheat sheet

Table 3: Joins

Joins are how you stitch normalized tables back together, and the join type controls exactly which unmatched rows survive. Beyond the everyday INNER and LEFT joins, this table covers LATERAL (great for correlated top-N-per-group results) and the one to avoid: NATURAL JOIN, which silently breaks when the schema changes.

Type	Example	Description
INNER JOIN	`FROM users u INNER JOIN orders o ON u.id = o.user_id`	• Returns only rows where the join condition matches in both tables • most common join type.

The rest of this section is part of the full cheat sheet

Table 4: Aggregate Functions

Aggregates collapse many rows into a single summary value, like a count, a sum, or an average, and they almost always pair with GROUP BY to produce one result per group. The real power here is in the less obvious tools: FILTER for conditional aggregation without CASE, STRING_AGG and ARRAY_AGG for collecting values, and GROUPING SETS, ROLLUP, and CUBE for computing multiple summary levels in a single pass.

Function	Example	Description
COUNT	`SELECT COUNT(*) FROM users`	• Counts rows • `COUNT()` counts all rows, `COUNT(column)` counts non-NULL* values only.

The rest of this section is part of the full cheat sheet

Table 1: Core Data Types

Type	Example	Description
INTEGER	`user_id INTEGER`	• 4-byte signed integer storing values from -2,147,483,648 to 2,147,483,647 • most common choice for IDs and counters.
BIGINT	`transaction_id BIGINT`	• 8-byte signed integer for very large numbers • essential for high-volume systems where INTEGER would overflow.
SMALLINT	`age SMALLINT`	2-byte signed integer (-32,768 to 32,767) — use for small-range values to save storage.
NUMERIC(p,s)	`price NUMERIC(10,2)`	• Exact arbitrary-precision decimal • use for financial calculations where floating-point errors are unacceptable.
REAL	`measurement REAL`	• 4-byte floating-point with ~6 decimal digits precision • faster than NUMERIC but inexact.
DOUBLE PRECISION	`latitude DOUBLE PRECISION`	• 8-byte floating-point with ~15 decimal digits precision • standard for scientific and geospatial calculations.
TEXT	`description TEXT`	• Unlimited-length string • identical performance to VARCHAR but without length limit — preferred unless you need length validation.
VARCHAR(n)	`name VARCHAR(100)`	• Variable-length string with optional maximum length • no performance advantage over TEXT — length is only a constraint.
CHAR(n)	`country_code CHAR(2)`	• Fixed-length string padded with spaces • rarely needed — only use for truly fixed-width data like codes.
BOOLEAN	`is_active BOOLEAN`	• Stores TRUE, FALSE, or NULL • accepts various input formats like `'t'`, `'yes'`, `'1'` for TRUE.
DATE	`birth_date DATE`	Calendar date without time — range from 4713 BC to 5874897 AD.
TIMESTAMP	`created_at TIMESTAMP`	• Date and time without timezone • stores the date and time exactly as written, with no timezone context, so the same value can mean different real moments depending on which zone it's read in.
TIMESTAMPTZ	`updated_at TIMESTAMPTZ`	• Date and time with timezone • converts to UTC internally — always prefer this over TIMESTAMP for consistency.
INTERVAL	`duration INTERVAL`	• Time span like `'3 days'` or `'2 hours 30 minutes'` • supports arithmetic with dates and timestamps.
UUID	`session_id UUID DEFAULT gen_random_uuid()`	• 128-bit universally unique identifier • use `uuidv7()` (PG18) for time-sortable IDs or `gen_random_uuid()` for random UUIDv4.
JSONB	`config JSONB`	• Binary JSON with full indexing support • slightly slower to insert but much faster to query — the standard choice over JSON.
JSON	`metadata JSON`	• Stores JSON as text, validates syntax • doesn't index efficiently — use JSONB instead.
ARRAY	`tags TEXT[]`	• Variable-length multidimensional array of any type • use bracket notation `[1]` for access.
BYTEA	`file_data BYTEA`	Binary data storage for images and files — stored as byte array with hex or escape encoding.
GENERATED ALWAYS AS IDENTITY	`id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY`	• SQL-standard auto-incrementing column • replaces SERIAL — sequence is owned by the column and cannot be accidentally overridden.
daterange / tstzrange	`valid_period tstzrange`	• Built-in range types for dates and timestamps • support overlap, contains, adjacent operators — essential for temporal data.
SERIAL (legacy)	`id SERIAL PRIMARY KEY`	• Auto-incrementing INTEGER via implicit sequence • use IDENTITY columns instead for new tables — SERIAL has ownership issues.

Table 2: Querying — SELECT and Filtering

Technique	Example	Description
SELECT columns	`SELECT id, name FROM users`	• Retrieves specified columns from a table • use `` for all columns but explicit column lists* are preferred.

Table 3: Joins

Type	Example	Description
INNER JOIN	`FROM users u INNER JOIN orders o ON u.id = o.user_id`	• Returns only rows where the join condition matches in both tables • most common join type.

Table 4: Aggregate Functions

Function	Example	Description
COUNT	`SELECT COUNT(*) FROM users`	• Counts rows • `COUNT()` counts all rows, `COUNT(column)` counts non-NULL* values only.