An atomic type defines the kind of value PostgreSQL can store and the operations permitted on it. Choosing the wrong type can silently change calculations, lose precision, or create migration errors. section 3.0 section 10.5
A common production mistake is dividing integer counts to calculate percentages. PostgreSQL truncates integral division toward zero, so 248650 divided by 250000 produces 0 rather than a fractional result. Cast at least one operand to float or double precision, then round the resulting numeric value when needed. section 2.5
Expose and correct integer division
Use pg_typeof() to inspect expression types: 10 / 4 returns 2 as integer, while 10::double precision / 4 returns 2.5 as double precision. For order percentages, cast the counts to float and round the calculated percentages to one decimal place. Also guard denominators with NULLIF; use COALESCE only when replacing an undefined result with a deliberate safe value. section 2.5
SELECT
10 / 4 AS "int division",
pg_typeof(10 / 4) AS "-> output type",
10::double precision / 4 AS "double division",
pg_typeof(10::double precision / 4) AS "-> output type"; int division | -> output type | double division | -> output type
--------------+----------------+-----------------+------------------
2 | integer | 2.5 | double precision
(1 row)
Compare PostgreSQL's integer and double-precision division while inspecting each expression's output type. section 2.5
