The Types Worth Knowing

Pick the narrowest type that fits. A type is a constraint, and a narrow one rejects wrong data at the door rather than letting it accumulate.

text for strings; Postgres has no performance reason to prefer varchar(n), and the length limit is a business rule better expressed as a CHECK.

timestamptz for a moment in time, never timestamp without the zone. Storing a moment without its zone is a bug you discover during a time change.

date for a day with no time, such as a birthday.

numeric for money. Never float: binary floating point cannot represent 0.1 exactly, and money that is out by a cent is a real problem.

boolean for true and false, rather than a 0 or 1 integer.

uuid where an identifier must not be guessable or must be generated before the row exists.

✦ Money in a float is a real bug, not a theoretical one

0.1 + 0.2 does not equal 0.3 in binary floating point, in any language. Sum enough transactions and the total disagrees with the sum of the parts, and no amount of rounding at display time fixes an accumulated error. Use numeric, or store integer cents.

Arrays and JSONB, used carefully. Postgres can store an array in a column, and JSONB can store an arbitrary document, indexed and queryable.

Both are genuinely useful and both are a way to smuggle an unmodelled relationship into a column. If you find yourself joining against something inside a JSONB field, that thing wanted a table. The honest use is data that is genuinely schemaless: a third-party payload you are storing verbatim, or per-record settings nobody queries across.