Appendix D — PostgreSQL Data Types Reference

A practical reference to the PostgreSQL types you'll actually use, with guidance on choosing the right one (the type is a correctness decision — Chapters 3, 8, 14).

Numbers

Type Use for Notes
smallint small whole numbers (±32K) 2 bytes
integer (int) whole numbers (±2.1B) the default integer
bigint large whole numbers 8 bytes; for big ids/counts
numeric(p,s) / decimal money, exact decimals exact; always for currency
real / double precision approximate floats (science, ratios) never for money (rounding drift)
serial / bigserial auto-increment (legacy) prefer GENERATED ... AS IDENTITY

Rule: money → numeric; "integer ÷ integer = integer" so cast for fractions (5.0/2).

Character / text

Type Use for Notes
text any string the usual choice; no length penalty
varchar(n) length-limited string use only when a limit is a real rule
char(n) fixed-length (padded) rarely useful

In PostgreSQL text and varchar perform identically — don't reflexively use varchar(255).

Date / time

Type Use for Notes
date calendar date
time time of day
timestamp date+time, no zone use only when you truly want no zone
timestamptz date+time with zone the right default for events/timestamps
interval a duration interval '7 days'

Use half-open ranges for periods (>= start AND < next_start); be deliberate about timestamp vs timestamptz (Ch. 8).

Boolean & UUID

Type Use for
boolean true / false / NULL (not 'Y'/'N')
uuid globally-unique ids (vs. integer identity)

Semi-structured & collections

Type Use for Notes
jsonb flexible/variable documents prefer over json; indexable (GIN), @>, ->> (Ch. 16)
json raw JSON text preserves exact text; not indexable; rarely needed
type[] (arrays) small ordered lists = ANY, @>, unnest; prefer a junction table for queried values
hstore key-value text pairs older; jsonb usually better

Ranges & specialized

Type Use for
int4range, numrange, daterange, tstzrange a range as one value; @>, &&; exclusion constraints (Ch. 16)
tsvector / tsquery full-text search (Ch. 16)
vector (pgvector ext.) AI embeddings / similarity (Ch. 36)
geometry / geography (PostGIS ext.) spatial data (Ch. 36)
inet / cidr / macaddr network addresses
bytea binary data

Identity / auto-generated keys

id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY      -- preferred; blocks manual values
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- allows manual values
-- serial/bigserial are legacy equivalents

Choosing — quick guidance

  • Money / exact → numeric. Approximate science → double precision.
  • Time → timestamptz (almost always). Date only → date.
  • Text → text (limit only for real rules).
  • Flags → boolean. Ids → integer/bigint identity (or uuid).
  • Variable/sparse data → jsonb (+ GIN). Small read-whole list → array; queried list → junction table.
  • Encode rules with constraints on the column (CHECK, NOT NULL, UNIQUE) — the type is the first line; constraints narrow the domain.

Type conversion

CAST(x AS type)   -- standard        x::type   -- PostgreSQL shorthand
to_char(n, fmt) / to_date(s, fmt) / to_number(s, fmt)   -- formatted

See also: Chapter 3 (domains), Chapter 8 (functions), Chapter 14 (DDL), Chapter 16 (JSONB/arrays/ranges), Appendix J (dialect differences). Full catalog: PostgreSQL Docs → "Data Types."