Choosing data types

Types enforce correctness, determine storage size and decide how comparisons and arithmetic behave.

Every column has a type, and choosing well prevents a whole category of bugs. The PostgreSQL types you will use most:

Integers: integer (4 bytes, about ±2.1 billion) and bigint (8 bytes). Use bigint for ids of tables that may grow; running out of integer ids in production is a painful migration.
Exact decimals: numeric(p, s), e.g. numeric(10,2) for prices. Use it for money.
Floating point: real and double precision are fast but inexact: 0.1 + 0.2 is not exactly 0.3. Fine for measurements, wrong for money.
Text: text and varchar(n). In PostgreSQL they perform identically; varchar(n) only adds a length check. Avoid char(n), which pads with spaces.
Booleans: boolean, with values true, false and NULL. MySQL emulates it with TINYINT(1); SQL Server uses bit.
Dates and times: date, timestamp (no time zone) and timestamptz. Prefer timestamptz for moments in time; it stores an absolute instant and converts to the session's time zone on display.
Identifiers: uuid for globally unique ids, plus jsonb, arrays and more, covered later.

Convert between types with the standard CAST(x AS type) or PostgreSQL's x::type. Literals such as '2026-03-01' are untyped until context gives them a type, which is why comparing them to a date column just works.

Dialects differ more here than anywhere else. SQLite uses type affinity and will happily store text in an integer column. Oracle uses NUMBER and VARCHAR2 and treats '' as NULL. MySQL's DATETIME has no time zone, while its TIMESTAMP converts to UTC and has a year-2038 limit.

Example

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint        NOT NULL,
    status      text          NOT NULL DEFAULT 'pending',
    total       numeric(10,2) NOT NULL,     -- exact: money
    is_gift     boolean       NOT NULL DEFAULT false,
    ordered_at  timestamptz   NOT NULL DEFAULT now()
);

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8;    -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3;          -- true

SELECT CAST('42' AS integer) + 1;                  -- 43
SELECT '2026-03-01'::date + 30;                    -- 2026-03-31

SELECT pg_typeof(total), pg_typeof(ordered_at) FROM orders LIMIT 1;
--  numeric | timestamp with time zone

Remember

Use numeric for money, never float
Default to bigint ids and timestamptz for points in time
text and varchar(n) perform the same in PostgreSQL
Cast with CAST(x AS t) or x::t

Watch out for

Storing prices in double precision and getting rounding errors in totals.
Using timestamp without time zone for events from users in different zones.

Check yourself

Which type should store an order total of 19.99?
In PostgreSQL, what is the practical difference between text and varchar(100)?

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

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks