# Choosing Data Types — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/i-types

> The right type prevents bugs.

## 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).

```sql
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.

**Quiz:** Which type should store prices?

- [ ] json
- [ ] float8
- [ ] text
- [x] numeric

*Answer:* numeric. Money needs exact decimals.