PostgreSQL Cheat Sheet

Updated 2026-04-21

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 293 indexed concepts. 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

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.

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.