Choosing Data Types

Exact numbers, dates, text, JSON, arrays

Use numeric for money and other exact decimals (floating point is approximate), bigint for ids, text for strings (no length penalty in PostgreSQL; add CHECK constraints for limits), date and timestamptz (time zone aware, the right default for events), boolean, jsonb for flexible attributes, arrays where small lists belong to one row, and uuid for ids generated outside the database. The right type gives correct arithmetic, comparisons and storage, and stops invalid values at the door.

Exact versus floating point and other types, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. 0.1 + 0.2 = 0.3 is true for numeric but false for float8. Adding 30 to a date gives a date; a JSONB value is extracted with ->; arrays and generated UUIDs work out of the box (gen_random_uuid is built in since PostgreSQL 13).

SELECT 0.1 + 0.2 = 0.3 AS numeric_exact,
       0.1::float8 + 0.2::float8 = 0.3::float8 AS float_exact,
       '2026-10-02'::date + 30 AS due_date,
       '{"a": 1}'::jsonb -> 'a' AS json_value,
       ARRAY[3, 1, 2] AS int_array,
       gen_random_uuid() IS NOT NULL AS has_uuid;

Output:

 numeric_exact | float_exact |  due_date  | json_value | int_array | has_uuid 
---------------+-------------+------------+------------+-----------+----------
 t             | f           | 2026-11-01 | 1          | {3,1,2}   | t
(1 row)

Use timestamptz for events

timestamptz stores an absolute moment and converts to the session time zone; plain timestamp loses that information.

Quick check: Which type should store prices?

• json
• float8
• text
• numeric