What is it? #
A column's type is a promise about what it may contain. PostgreSQL enforces that promise, which is why a well-typed table quietly prevents whole categories of bug.
Most columns need only a handful of types: a whole number, a decimal number, text, a true/false flag, and a point in time. The rest exist for specific jobs.
Three choices genuinely matter and are hard to change later: use numeric for money, never a floating-point type; use timestamptz for points in time, not timestamp; and use text rather than agonising over varchar lengths.
Get those three right and most type decisions afterwards are minor.
Think of it like this #
Types are the shape of the hole you are putting things into.
A round hole will not accept a square peg, and that is the point — the mistake is caught at the door instead of three months later in a report. Choosing numeric for money is choosing a hole shaped precisely for exact amounts, rather than one that is almost the right shape and quietly rounds the corners off every coin that passes through.
Simple example #
An orders table needs: an id, the amount paid, whether it has shipped, when it was placed, the delivery city, and some flexible extra details that differ per order.
That is one identity column, one numeric, one boolean, one timestamptz, one text and one jsonb — six columns covering six different type families.
Code #
-- ---------- Numbers ----------
integer -- whole numbers, about -2.1 billion to +2.1 billion. The default choice.
bigint -- whole numbers, astronomically large. Use for ids on big tables.
smallint -- -32768 to 32767. Rarely worth the saving.
numeric(10,2) -- EXACT decimal: 10 digits total, 2 after the point. USE FOR MONEY.
numeric -- exact decimal with no fixed limit
real -- 4-byte floating point \
double precision -- 8-byte floating point / APPROXIMATE. Never use for money.-- ---------- Why money must not be a floating-point type ----------
SELECT 0.1::double precision + 0.2::double precision; -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric; -- 0.3
-- Floating point stores binary fractions, so many decimal values are approximate.
-- Across a million transactions those tiny errors accumulate into real money.
-- Rule: money, quantities billed, tax -> numeric. Measurements, averages -> float is fine.-- ---------- Text ----------
text -- any length. The normal choice in PostgreSQL.
varchar(50) -- same as text, but rejects anything longer than 50
char(10) -- fixed length, PADDED WITH SPACES. Almost never what you want.
-- In PostgreSQL, text and varchar perform identically. varchar(n) only adds a limit.
-- Use varchar(n) when the limit is a real business rule (e.g. a 2-letter country code).
-- Use text otherwise, and enforce real rules with a CHECK constraint.-- ---------- Dates and times ----------
date -- a calendar day, no time e.g. 2026-01-31
time -- a time of day, no date e.g. 14:30:00
timestamp -- date + time, NO time zone <- usually the wrong choice
timestamptz -- date + time, time-zone aware <- USE THIS ONE
interval -- a LENGTH of time e.g. '7 days', '2 hours'
-- timestamptz stores an absolute point in time and converts it to the
-- client's time zone on display. timestamp stores a wall-clock reading
-- with no record of where in the world it was read.
SELECT now(); -- current timestamptz
SELECT now() - interval '7 days'; -- a week ago
SELECT date_trunc('month', now()); -- first moment of this month
SELECT age(timestamptz '2000-01-01'); -- how long since that date-- ---------- Other everyday types ----------
boolean -- true / false / NULL
uuid -- a 128-bit identifier, e.g. gen_random_uuid()
json -- JSON stored as TEXT: keeps formatting, re-parsed on every read
jsonb -- JSON stored in a binary form: faster to query, INDEXABLE
-- Use jsonb unless you specifically need the original text preserved.
text[] -- an array of text values-- ---------- jsonb in practice ----------
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
total numeric(10,2) NOT NULL,
shipped boolean NOT NULL DEFAULT false,
placed_at timestamptz NOT NULL DEFAULT now(),
city text,
details jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO orders (total, city, details)
VALUES (450.00, 'Pune', '{"gift_wrap": true, "coupon": "NEW10"}');
SELECT details ->> 'coupon' AS coupon -- ->> returns text
FROM orders;
SELECT details -> 'gift_wrap' AS gift_wrap -- -> returns jsonb
FROM orders;
SELECT * FROM orders
WHERE details @> '{"gift_wrap": true}'; -- @> means "contains"-- ---------- Enums: a fixed list of allowed values ----------
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');
ALTER TABLE orders ADD COLUMN status order_status NOT NULL DEFAULT 'pending';
-- Adding a new value later is easy:
ALTER TYPE order_status ADD VALUE 'refunded';
-- Removing or reordering values is NOT easy — it needs a type rebuild.
-- A text column with a CHECK constraint is more flexible and often the better call:
-- status text NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled'))-- ---------- Arrays ----------
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tags text[] NOT NULL DEFAULT '{}'
);
INSERT INTO articles (tags) VALUES (ARRAY['sql', 'postgres']);
SELECT * FROM articles WHERE 'sql' = ANY(tags);
-- Arrays are convenient for a short, fixed list that belongs to one row.
-- If you need to query, count or join on the values, a proper related table is better.How it works #
numeric versus floating point is the single most consequential choice here. A floating-point type stores numbers in binary, and many ordinary decimal values — 0.1 among them — have no exact binary representation. The result is that 0.1 + 0.2 really does come out as 0.30000000000000004. For a physics measurement that is irrelevant. For money it is a defect that compounds silently across every transaction until an accountant notices the ledger is off. numeric stores digits exactly, costs a little performance, and is the right answer for anything a customer will be billed for.
timestamptz versus timestamp is the second. Despite the name, timestamptz does not store a time zone; it stores an absolute moment, normalised to UTC, and converts to the viewer's zone on display. timestamp stores a wall-clock reading with no indication of where it was taken — "14:30" in a database used from two countries is genuinely ambiguous, and daylight-saving transitions make it worse. Almost every "when did this happen" column should be timestamptz. Use plain timestamp only for something like a shop's opening hours, where the local wall-clock reading is the actual fact.
text versus varchar(n) trips up people arriving from other databases where varchar is faster. In PostgreSQL they are the same underneath; varchar(n) merely adds a length check. Since guessing a maximum length is usually arbitrary — and changing it later requires altering the table — text is the sensible default. Reach for varchar(n) only when the limit is a genuine rule.
jsonb versus json is straightforward: json keeps the raw text and re-parses it on every read; jsonb parses once into a binary form that is faster to query and, crucially, can be indexed with GIN. Choose jsonb unless you must preserve key order and whitespace exactly.
Enums are a real type with a fixed list of values. Adding a value is cheap; removing or reordering one is not, because the type must be rebuilt. A text column with a CHECK constraint gives you the same safety with far easier changes, which is why many teams prefer it.
Use bigint for primary keys on anything that might grow. integer runs out at about 2.1 billion, and converting a busy table's key type afterwards is a painful, locking operation. The eight bytes are cheap insurance.
Real-world use #
The types you pick on day one are the hardest thing to change on day four hundred. Changing a column's type rewrites the table and takes a lock, which on a large production table means planned downtime or a careful multi-step migration. This is the moment to be deliberate.
uuid primary keys are popular when ids must be generated outside the database or must not reveal how many records exist. The trade-off is that random UUIDs scatter inserts across the index instead of appending neatly, which costs write performance on large tables. bigint identity columns remain the better default unless you have a specific reason.
jsonb is excellent for genuinely variable data — per-order options, webhook payloads, feature flags. It is a poor substitute for columns you query and filter on constantly. If you find yourself reaching into the same JSON key in every query, that key wants to be a real column with a real index.
Arrays are the same story at a smaller scale: fine for a short tag list attached to one row, wrong when you need to count, join or enforce references. That is a related table's job.
A practical note on storage: PostgreSQL automatically compresses and moves oversized values out of the main table into what is called TOAST storage. This means a text column holding an occasional very large document does not slow down queries that never read it. The storage lesson covers this.
Common mistakes #
- Using real or double precision for money, which accumulates rounding errors that cannot be recovered.
- Using timestamp instead of timestamptz, making stored times ambiguous across time zones and DST changes.
- Choosing an arbitrary varchar(n) length that later needs a table-rewriting migration to raise.
- Using integer for a primary key on a table that eventually exceeds 2.1 billion rows.
- Storing data in jsonb that is queried in every request, instead of promoting it to a real indexed column.
Practice #
Design a payments table and justify every type out loud: an id, the amount charged, the currency code, whether it succeeded, when it was attempted, the gateway's raw response, and a status limited to a fixed set of values. Create it, insert a few rows, then prove to yourself why the amount column must be numeric by running SELECT 0.1::double precision + 0.2::double precision; and comparing it with the numeric version.