Types that fit the data

Numbers: exact and approximate

PostgreSQL has two families of decimal number, and choosing between them is a decision with consequences. numeric(10, 2) stores exactly what you wrote — ten digits with two after the point — and is what money uses. double precision stores the nearest binary float, which is faster and cannot represent 0.1 exactly; it is for measurements, not for amounts.

The integer family is the same story with whole numbers: smallint, integer and bigint are two, four and eight bytes. An id is integer (or bigint when it will grow for decades), a count is integer, and a year is smallint if you enjoy precision.

Text, truth, and time

text is unbounded text, and in PostgreSQL it is not slower than varchar(n) — the length-limited form exists to enforce a limit, not for speed, so the limit should mean something when you write it. boolean is true or false, spelled exactly like that, with NULL as the third possibility.

For time there are two types and one rule: timestamptz (with a time zone) stores an instant, and timestamp (without) stores a reading on a clock. Almost every application should use timestamptz, because an instant is what actually happened. date is a calendar day with no time, and interval is a duration — interval '30 days' — which, unlike a subtraction of two dates you did by hand, understands months and leap years.

Casts, and one exotic type

A value can be converted with :: — '42'::integer, 123.456::numeric(6, 2) — and the conversion refuses when it would be nonsense, which is the useful part. Text concatenation is ||, and to_char formats numbers and dates for display: to_char(1234.5, 'FM9,999.99') gives 1,234.50.

jsonb stores JSON, validated and searchable, and it is one of the reasons people reach for Postgres when the shape of the data is not fixed yet. Use it for the parts that genuinely have no columns; a table of real columns enforces more and reads better, and the honest rule is that a column should become jsonb for a reason you could explain.

What numeric actually stores

numeric keeps a decimal number as digits and a scale, not as a binary approximation, so 0.10 is stored as exactly one tenth. The declaration numeric(10, 2) fixes the total digits and the digits after the point; a value with more decimals is rounded to fit, and a value with too many digits before the point is an error.

That rounding is the reason a money column must be numeric and not double precision. A float stores the nearest binary fraction, so a long chain of additions can end at 0.30000000000000004 — a number no invoice should print. Exactness costs a little space and speed, and for money it is the right trade.

A timestamp is a point, a clock reading is not

timestamptz stores an instant in UTC and converts it for display using the session's time zone. Two rows written in different cities with the same instant therefore compare equal, and sorting them puts them in the order things actually happened. timestamp stores the digits on the clock with no zone attached, so the same value can mean two different moments.

An interval is a duration with three independent parts — months, days and microseconds — because a month is not a fixed number of days. That is why date '2026-01-31' + interval '1 month' lands on the last day of February rather than on a date three weeks later.

Try these next

• Insert a value with three decimals into a numeric(10, 2) column and see what comes back.
• Add a date column and compare it with a timestamptz column holding the same day.
• Store a jsonb object with a nested field, then read it with -> and ->>.

Columns with the right types

CREATE TABLE expenses (
    id integer,
    label text,
    amount numeric(10, 2),
    weight_kg double precision,
    paid boolean,
    spent_at timestamptz,
    receipt jsonb
);

INSERT INTO expenses VALUES
    (1, 'Lunch', 14.25, 0.4, true,  '2026-09-01 12:30+02', '{"vendor": "Cafe"}'),
    (2, 'Train', 42.50, NULL, false, '2026-09-02 08:05+02', NULL);

-- Exact money: the arithmetic a float would get wrong.
SELECT label, amount / 3 AS third FROM expenses WHERE id = 1;

-- A cast refuses nonsense, which is the point of asking for it.
SELECT '42'::integer + 1 AS answer;

-- Formatting is a display decision, made at the last moment.
SELECT label, to_char(amount, 'FM9,999.99') AS money FROM expenses;

-- JSON field read out of a jsonb column.
SELECT receipt ->> 'vendor' AS vendor FROM expenses WHERE id = 1;