Types and casting, where the arithmetic quietly goes wrong
Integer division, numeric versus float, the :: cast, and the casts that truncate or fail instead of doing what you meant.
After this lesson you can
- Predict the type of an arithmetic expression before you run it
- Choose numeric or double precision for a column and justify it
- Cast safely with :: and check input with pg_input_is_valid
- Compute a percentage without integer division
PostgreSQL is strictly typed. Every expression has a type decided before a single row is read, and the type decides the arithmetic. Most "wrong number" bugs in reports are a type the author did not notice.
The type decides the answer
An integer divided by an integer is an integer: the fraction is thrown away,
not rounded. A literal with a decimal point, such as 2.0, is numeric,
and one numeric operand is enough to make the whole division exact.
pg_typeof tells you what PostgreSQL decided.
SELECT 7 / 2 AS int_div, 7 / 2.0 AS numeric_div, 7::numeric / 2 AS cast_first, 7 % 2 AS remainder, pg_typeof(7 / 2) AS t_int, pg_typeof(7 / 2.0) AS t_numeric, pg_typeof(7::float8 / 2) AS t_float;Casting is expr::type (PostgreSQL's shorthand) or the standard
CAST(expr AS type); they do the same thing. Cast before you divide.
(7 / 2)::numeric casts an integer that is already 3.
numeric versus double precision
numeric is exact decimal arithmetic with as many digits as you ask for.
double precision (float8) is binary floating point: fast, but values
such as 0.1 have no exact representation. They also round differently:
round() on numeric rounds a half away from zero, while on float8 the
tie is settled by the platform's floating-point rounding, which on common
hardware (this sandbox included) means the nearest even number. Money,
quantities and anything you will compare with = belong in numeric; measurements where a tiny
relative error is fine can be float8.
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8 AS float_equal, 0.1 + 0.2 = 0.3 AS numeric_equal, round(2.5) AS numeric_round, round(2.5::float8) AS float_round, round(3.5::float8) AS float_round_35, round(-2.5) AS numeric_round_neg, pg_input_is_valid('42', 'integer') AS ok_int, pg_input_is_valid('3000000000', 'integer') AS too_big, pg_input_is_valid('2024-02-30', 'date') AS bad_date;The same example shows pg_input_is_valid(text, type), new in PostgreSQL
16: it answers "would this cast succeed?" without raising an error, which
is what you want when cleaning imported text.
Casts that fail, and one that silently truncates
The example below uses a small helper, attempt(sql), defined in its
setup: it runs a statement and returns ok or the SQLSTATE and message
instead of aborting. Note the difference between the first two columns.
Inserting a six-character string into a varchar(3) column is an error.
An explicit cast of the same string to varchar(3) is not: the SQL
standard says it truncates, and PostgreSQL does.
-- Runs a statement and reports its error instead of aborting.CREATE FUNCTION attempt(stmt text) RETURNS text LANGUAGE plpgsql AS $$BEGIN EXECUTE stmt; RETURN 'ok';EXCEPTION WHEN OTHERS THEN RETURN SQLSTATE || ': ' || SQLERRM;END $$;CREATE TABLE codes (code varchar(3));SELECT attempt($$INSERT INTO codes VALUES ('abcdef')$$) AS insert_too_long, 'abcdef'::varchar(3) AS explicit_cast, attempt($$SELECT 2147483647 + 1$$) AS int_overflow, attempt($$SELECT 'abc'::int$$) AS bad_cast, attempt($$SELECT round(2.345::float8, 2)$$) AS float_round_digits;round(double precision, integer) does not exist, which is why
round(avg(x)::float8, 2) fails while round(avg(x), 2) works:
avg of an integer column already returns numeric.
Aggregates have types too
count(*) and sum(int) return bigint; avg(int) returns numeric.
A percentage written 100 * hits / total with integer columns is integer
division, and two out of three becomes 66, not 66.7. Write
round(100.0 * hits / total, 1) and the first operand makes the whole
expression numeric.
Common mistakes
- Writing
hits / totalwith two integer columns and getting 0 for every ratio below one. The division ran in integers before anything else. - Casting after the division:
(a / b)::numerickeeps the truncated integer and only changes how it is displayed. - Storing money in
realordouble precision, then finding that a sum compared with=to a known total is .
Try it yourself
2 visible tests · 2 hidden testsTable imports(id, batch, qty_text) holds quantities loaded from CSV
files as raw text (never NULL). A value is valid when casting it to
integer would succeed; anything else (a decimal, a word, an empty
string, a number too large for integer) is invalid and must not stop
the query. For every batch return batch, total_rows (all its rows),
valid_rows (rows whose value is valid) and total_qty: the sum of its
valid quantities, or 0 when it has none. The sum may exceed the
integer range. Order by batch.
CREATE TABLE imports (id int PRIMARY KEY, batch text NOT NULL, qty_text text NOT NULL);INSERT INTO imports VALUES (1, 'march', '12'), (2, 'march', 'twelve'), (3, 'april', '7'), (4, 'march', '3'), (5, 'april', '2.5'), (6, 'april', '-4');Sign up to run the tests, check the hidden ones and save your progress. Sign up