An integer represents a whole-number value without a fractional component. In PostgreSQL, integer division truncates toward zero, even when the intended result is fractional. section 2.5 section 10.5
The percentage that becomes zero
Dividing integer order counts can produce 0 instead of a useful percentage: 1350 / 250000 evaluates to 0. Cast at least one operand to double precision or float, then round the resulting percentage as needed. section 2.5
Use PostgreSQL's type behavior deliberately:
- Inspect an expression with pg_typeof() and use :: as shorthand for casting. section 2.5
- Guard integer divisors with NULLIF to produce NULL, or combine it with COALESCE to substitute a safe value. section 2.5
- During migrations, map integer, numeric, and floating-point types explicitly rather than trusting matching names across database systems. section 10.5
WITH
i AS (
SELECT count(*) c
FROM erp.orders
WHERE item IS NOT NULL
),
s AS (
SELECT count(*) c
FROM erp.orders
WHERE service IS NOT NULL
)
SELECT
i.c AS "Item orders",
s.c AS "Service orders",
i.c + s.c AS "Total",
i.c / (i.c + s.c) * 100 AS "Item order %",
s.c / (i.c + s.c) * 100 AS "Service order %"
FROM i, s;
Inspect the order-count query whose integer divisions silently produce incorrect percentages. section 2.5
