08 / 32 · 5 min

Choosing a type, and the trouble with nothing

A column's type is a constraint, and the cheapest one available. It is checked on every write, costs nothing at runtime, and rules out an entire category of bad data permanently.

Picking one

Need	Use	Not
Money	`NUMERIC(10,2)`	`FLOAT`, which cannot hold 0.10 exactly
A moment in time	`TIMESTAMPTZ`	`TIMESTAMP`, which has no zone
Just a date	`DATE`	a timestamp at midnight
Text	`TEXT` or `VARCHAR(n)`	`CHAR(n)`, which pads with spaces
Yes or no	`BOOLEAN`	an integer, or worse a string
An identifier	`BIGINT` or `UUID`	`INT`, which runs out at 2.1 billion

The money row is the one that costs real organisations real money. Binary floating point cannot represent most decimal fractions, so adding 0.1 ten times does not give 1.0, and a ledger that does this drifts.

The identifier row is a common outage. A 32 bit integer key is fine until the table passes two billion rows, at which point inserts fail and changing the type requires rewriting the table.

NULL is not a value

NULL means unknown. It is not zero, not an empty string, and not false, and every comparison with it gives unknown rather than true or false.

sql

SELECT NULL = NULL;      -- NULL, not true
SELECT NULL <> 5;        -- NULL, not true
SELECT * FROM t WHERE c = NULL;    -- always zero rows
SELECT * FROM t WHERE c IS NULL;   -- correct

This propagates. WHERE NOT (amount > 100) does not include rows where amount is NULL, because the inner condition was unknown, and the negation of unknown is still unknown.

Aggregates skip it: AVG(amount) over three values and two NULLs divides by three. That is often what you want and is worth being conscious of.

Three valued logic

A	B	A AND B	A OR B
true	NULL	NULL	true
false	NULL	false	NULL
NULL	NULL	NULL	NULL

false AND NULL is false, because one false is enough. true OR NULL is true for the same reason. The rest are unknown.

The practical advice

Default to NOT NULL. Allow NULL only where "we genuinely do not know" is a state the column needs, and when it is, write down what it means, because "no middle name" and "we never asked" are different facts stored identically.