Choosing core data types

Types are hard to change on big tables, and the right ones prevent bugs like rounding errors in money.

PostgreSQL has a rich type system; a few choices cover most columns.

Text: use text. varchar(n) is the same type with a length check, and char(n) pads with spaces — avoid it. If you need a limit, a CHECK (length(x) <= n) is easier to change later. Performance is identical.
Integers: integer (4 bytes, up to about 2.1 billion) and bigint (8 bytes). Use bigint for primary keys of tables that may grow large; running out of integer ids is a classic outage. smallint rarely pays off.
Exact decimals: numeric(p, s) for money and anything that must add up exactly. real and double precision are binary floating point: fast, approximate, fine for measurements. Avoid the money type; it depends on the lc_monetary setting.
boolean: true, false or NULL.
Binary: bytea. For large files, store them in object storage and keep the path.
Dates and times: date, and timestamptz (next lessons).

Also useful: uuid, jsonb, arrays, inet/cidr for IP addresses, and range types — covered later. Use the :: operator (or standard CAST) to convert: '42'::int. pg_typeof(expr) tells you the type of any expression, which is handy when an operator complains it doesn't exist for your types.

Example

CREATE TABLE payment (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reference text NOT NULL CHECK (length(reference) <= 40),
    amount    numeric(12, 2) NOT NULL,
    refunded  boolean NOT NULL DEFAULT false,
    client_ip inet
);
INSERT INTO payment (reference, amount, client_ip)
VALUES ('INV-1', 0.10, '203.0.113.7'), ('INV-2', 0.20, NULL);

SELECT sum(amount) FROM payment;                   -- 0.30 (exact)
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8;    -- f   (binary floats)
SELECT pg_typeof(amount), pg_typeof('42'::int) FROM payment LIMIT 1;
-- numeric | integer

Remember

text everywhere; add a CHECK if you need a max length
bigint for ids that may grow; numeric for money
Floats are approximate; never compare them for exact equality

Watch out for

Using integer primary keys on a fast-growing table and hitting 2,147,483,647.

Check yourself

Which type should store invoice totals?

Official docs: www.postgresql.org/docs/current/datatype.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks