PostgreSQLpostgresqldata typestimestamptznumerictext

PostgreSQL: Tables, Types and the Catalog

PostgreSQL’s type system is where much of its power hides — timestamptz, numeric, arrays, ranges, enums, JSONB — and choosing the right type is the cheapest correctness and performance decision you will make.

Interview question See how this works internally →

Progress

The types that matter

bigint for ids and counts — int overflows at 2.1 billion and someone always hits it. numeric(p, s) for money; never float, which cannot represent 0.10 exactly. text for strings; varchar(n) buys you nothing but a constraint you will regret, and char(n) pads. timestamptz for every point in time; plain timestamp has no zone and is a bug waiting for a server in another region. date when the time is genuinely irrelevant. boolean, not char(1) or int. uuid when ids must be generated without coordination.

GENERATED ALWAYS AS IDENTITY is the modern way to get an auto-incrementing key; it replaces serial, which was a sequence bolted on with a default. GENERATED ALWAYS AS (expr) STORED computes a column from others and keeps it updated — a lower-case email, a total from quantity and price — and is indexable.

Types other databases do not have

Arrays (text[], int[]): a bounded list inside a row, indexable with GIN for containment (@>). Right for tags; wrong for anything you would join on. Ranges (tstzrange, int4range): a lower and upper bound as one value, with overlap operators and the EXCLUDE constraint that prevents double-booking. Enums: a fixed set of labels stored as 4 bytes; adding a value is easy, removing one is not — a lookup table is more flexible, an enum is faster and self-documenting. JSONB: a binary, indexable document inside a column — see JSONB, Full-Text Search and Extensions. Composite types, domains (a type with a CHECK baked in), and inet/cidr for addresses.

Types doing work

1CREATE TYPE order_status AS ENUM ('pending','paid','shipped','cancelled','refunded');
2CREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@]+@[^@]+$');
3 
4CREATE TABLE orders (
5  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
6  status      order_status NOT NULL DEFAULT 'pending',
7  total       numeric(12,2) NOT NULL,
8  tags        text[] NOT NULL DEFAULT '{}',
9  valid       tstzrange NOT NULL,
10  contact     email NOT NULL,
11  total_cents bigint GENERATED ALWAYS AS ((total * 100)::bigint) STORED
12);
13CREATE INDEX orders_tags ON orders USING gin (tags);

The catalog

Everything PostgreSQL knows about your schema lives in ordinary tables you can query: pg_class (tables and indexes), pg_attribute (columns), pg_index, pg_constraint, pg_stats (the planner’s statistics). The information_schema views are the portable subset. \d tablename in psql is a query over them. When you want to know "which tables have no primary key", "which indexes are unused", "which columns are nullable", the catalog answers with SQL.

Key points

bigint, numeric, text, timestamptz, boolean, uuid — the defaults. Never float for money, never timestamp without tz.
Arrays, ranges, enums, JSONB and domains are real modelling tools, each with an index type.
GENERATED … AS IDENTITY for keys; STORED generated columns for derived values.
The catalog is queryable; use it.