SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Rounding, truncation, and integer division surprises
Predict when SQL throws away a fraction: integer division, and the different directions ROUND, TRUNC, FLOOR and CEIL move positive and negative values.
What you will learn
- Spot integer division and fix it by casting to numeric or multiplying before dividing
- Choose ROUND, TRUNC, FLOOR or CEIL by how each one treats negative values
- Explain why ROUND(2.5) is 3 on numeric but 2 on double precision
- Round money once at the end on numeric instead of at every intermediate step
Understanding Rounding, truncation, and integer division surprises
SQL resolves arithmetic by operand type before it computes a value. When both sides of / are integers, PostgreSQL, SQL Server and SQLite choose the integer division operator, which discards the fractional part at that step and hands the next operator a whole number. That is why 3 / 4 * 100 is 0 and not 75: the value 0.75 never exists, so nothing downstream, neither * 100 nor ROUND, can recover it. The repair has to happen upstream, by promoting one operand to numeric or by arranging for the multiplication to run first.
ROUND, TRUNC, FLOOR and CEIL answer four different questions about direction, and they only disagree on negatives and on exact ties. TRUNC deletes digits, which moves toward zero, so TRUNC(-2.7) is -2, while FLOOR always moves toward negative infinity, so FLOOR(-2.7) is -3. ROUND picks the nearer integer and, on numeric, breaks a .5 tie by moving away from zero, so 2.5 becomes 3 and -2.5 becomes -3. Pick the function whose direction rule matches the business rule: a partly filled shipping box is CEIL, whole completed years is FLOOR, a displayed price is ROUND.
Type also decides how ties and fractions behave. numeric stores decimal digits exactly, so 2.5 really is 2.5 and the away-from-zero tie rule applies, whereas double precision stores a binary approximation and breaks ties with the platform's rint, normally to the nearest even integer, which is why ROUND(2.5::float8) is 2 but ROUND(3.5::float8) is 4. Binary drift also accumulates: 0.1::float8 + 0.2::float8 prints 0.30000000000000004. Keep money and quantities in numeric, and round once where the value is displayed, because every intermediate rounding adds an error the next step cannot tell apart from real data.
-- PostgreSQL
SELECT 3 / 4 AS int_div,
3 / 4 * 100 AS pct_wrong,
3 * 100 / 4 AS pct_reordered,
ROUND(3 * 100.0 / 4, 1) AS pct_promoted;Operand types decide the arithmetic, so precision is lost at the operation itself, before any rounding function can see the value.
Worked examples
Four directions, one number
Shows where ROUND, TRUNC, FLOOR and CEIL agree and where negatives pull them apart.
SELECT amount,
ROUND(amount) AS rounded,
TRUNC(amount) AS truncated,
FLOOR(amount) AS floored,
CEIL(amount) AS ceiled
FROM (VALUES (2.5), (-2.5), (-2.4)) AS t(amount);Example explained
Line 1ROUND(2.5) is 3 and ROUND(-2.5) is -3 because numeric breaks a tie by moving away from zero, so the magnitude grows in both directions.
Line 2TRUNC(-2.5) and TRUNC(-2.4) are both -2: truncation only deletes digits, it never touches the integer part.
Line 3FLOOR(-2.4) is -3 while TRUNC(-2.4) is -2, and this is the pair most bugs come from, since they are identical for every positive input you test with.
Line 4CEIL(-2.5) is -2, not -3, because up means toward positive infinity rather than larger magnitude.
The same 2.5, two tie rules
Demonstrates that the type of the value, not the ROUND call, decides how a .5 tie is broken.
SELECT ROUND(2.5) AS numeric_2_5,
ROUND(3.5) AS numeric_3_5,
ROUND(2.5::float8) AS float_2_5,
ROUND(3.5::float8) AS float_3_5;Example explained
Line 1A literal like 2.5 is numeric, so round(numeric) applies the away-from-zero tie rule and returns 3.
Line 2The ::float8 casts hand the same digits to double precision, whose tie rule is round-half-to-even, so 2.5 falls to 2 while 3.5 still rises to 4.
Line 3Ties are therefore not a property of the function; two columns holding what looks like the same number can round in opposite directions.
Line 4PostgreSQL has no round(double precision, integer) at all, so ROUND(2.5::float8, 1) raises a function-does-not-exist error instead of quietly using the other rule.
Negative integer division and its remainder
Shows that integer division truncates toward zero rather than flooring, and that % takes the sign of the dividend.
SELECT -7 / 2 AS int_div,
-7 % 2 AS remainder,
(-7 / 2) * 2 + (-7 % 2) AS reconstructed,
FLOOR(-7 / 2.0) AS floor_div;Example explained
Line 1-7 / 2 is -3, not -4, because integer division truncates toward zero and so matches TRUNC rather than FLOOR.
Line 2-7 % 2 is -1 because the remainder carries the sign of the dividend, which is why x % 2 = 1 fails to detect odd negative numbers and x % 2 <> 0 is the safe test.
Line 3The third column checks the identity the two operators must satisfy together: (a / b) * b + (a % b) rebuilds a exactly, here -6 + -1 = -7.
Line 4FLOOR(-7 / 2.0) is -4 because 2.0 makes the division numeric, so the full -3.5 reaches FLOOR instead of being truncated first.
Important notes
The division rule is dialect-specific: 7 / 2 is 3 in PostgreSQL, SQL Server and SQLite, but 3.5 in MySQL, where the integer operator is DIV, and 3.5 in Oracle, whose NUMBER type has no integer division. Re-run any division-heavy query after a migration.
PostgreSQL numeric division carries extra digits so it is never less accurate than float8, which is why 1::numeric / 3 prints twenty 3s; wrap the result in ROUND with a scale whenever the value is being displayed.
Common mistakes
Writing SUM(qty) / COUNT(*) to get an average: sum() returns bigint and count() returns bigint, so PostgreSQL performs integer division and reports 3 where AVG(qty) reports 3.75.
Assuming CAST(x AS int) truncates. PostgreSQL rounds, so CAST(2.7 AS int) is 3 and CAST(2.5 AS int) is 3, while SQL Server truncates 2.7 to 2; the identical query changes totals when it is ported. Write TRUNC or FLOOR when truncation is what you mean.
Rounding before aggregating: SUM(ROUND(price * qty, 2)) and ROUND(SUM(price * qty), 2) can differ by cents, so a line-item report and its grand total stop reconciling. Choose which one is authoritative and use only that form.
Try it yourself
Change, predict, then run
Run SELECT 5 / 2 * 100; and confirm it returns 200, then write two queries that return 250, one by reordering the operators and one by promoting an operand to numeric. Finish with a single SELECT comparing ROUND(-0.5), TRUNC(-0.5) and FLOOR(-0.5) and explain each result.
Open the SQL workspaceCheck your understanding
In PostgreSQL, score is an integer column. SELECT ROUND(score / 10 * 100, 2) FROM t returns 0.00 for a row where score is 7. What is happening?
- ROUND with a scale of 2 collapses any value below 1 to 0.00
- score / 10 is integer division, so it evaluates to 0 before * 100 or ROUND ever run
- Multiplication binds tighter than division, so 10 * 100 is computed first and 7 / 1000 rounds to 0.00
- ROUND returns double precision, which cannot hold a value derived from an integer column
Show answer
Both operands of / are integers, so the engine uses integer division and 7 / 10 becomes 0 at that step; every later operator only ever sees a plain 0, so ROUND has nothing left to recover. Option 3 is tempting because reordering really does fix the bug, but * and / have equal precedence and evaluate left to right, so the division genuinely runs first; score * 100 / 10 works because the integer 700 is formed before anything is discarded.