String functions were for presenting. Numeric ones are for calculating, and there the margin for error stops being cosmetic: a badly done rounding doesn't just look bad, it costs money. This lesson covers the scalar functions that work with numbers and, above all, the two traps separating a correct report from one that's out by cents: integer division and .5 rounding. You've been using ROUND since module 2 with no explanation; here it's time to really understand it, including the uncomfortable part: in PostgreSQL, ROUND(2.5) and ROUND(2.5::DOUBLE PRECISION) don't return the same thing.

1. Rounding and truncating: ROUND, TRUNC, CEIL, FLOOR
2. .5 rounding: NUMERIC against DOUBLE PRECISION
3. Arithmetic: ABS, SIGN, MOD, POWER, SQRT, logarithms
4. GREATEST and LEAST aren't MAX and MIN
5. Integer division and its three fixes
6. Precision: GreenStore's lost cent
7. Real cases: VAT, margin, discounts and shipping
8. RANDOM and generate_series
9. Comparison table by engine
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

With negative numbers all four part company, and that's where people get it wrong:

TRUNC cuts towards zero (-12.789 → -12), so it agrees with CEIL on negatives and with FLOOR on positives; FLOOR always goes downwards, towards -∞ (-13); and ROUND goes to the nearest, which here is also -13.

A type trap. ROUND(x, n) and TRUNC(x, n) only exist for NUMERIC. If x is DOUBLE PRECISION, PostgreSQL fails with ERROR: function round(double precision, integer) does not exist. The fix is ROUND(x::NUMERIC, 2), and the fact that the two-argument version doesn't exist for floating point isn't an oversight: it's the engine telling you that rounding a DOUBLE PRECISION to two decimals doesn't mean what you think it does. Section 6 proves it.

When the value falls exactly on the halfway mark, a direction has to be chosen. And PostgreSQL chooses differently depending on the type:

Two different answers to the same question. The literals 0.5 and 2.5 are NUMERIC, and NUMERIC rounds half-up (half away from zero). Converting them to floating point, PostgreSQL delegates to the system's rint() function, which rounds half-even: to the nearest even number.

Neither is "the correct one". Half-even doesn't bias sums —half the ties go up and half go down— and half-up is the one taught at school and the one most accounting regulations demand. The problem isn't choosing badly, it's choosing without knowing: if half your report uses NUMERIC and the other half DOUBLE PRECISION, your totals won't agree with each other and nobody will know why.

The course's rule: money is NUMERIC (01-04). Always round NUMERIC, and that way the .5 goes up. If you receive a DOUBLE PRECISION from outside, convert it with ::NUMERIC before rounding, not after.

And an important clarification: the famous ROUND(1.005, 2) behaves properly with NUMERIC —it returns 1.01— because 1.005 is stored exactly. In DOUBLE PRECISION that literal is really 1.00499999999999989… and it would round to 1.00. It isn't a ROUND bug: it's that the number you gave it is no longer 1.005.

Two warnings about that table. The results you see are the mathematical values: PostgreSQL computes these functions over NUMERIC with 16 significant digits, so SELECT SQRT(16); actually displays 4.0000000000000000. It isn't an error, it's the working precision; wrap it in ROUND when presenting. And LOG with no base is base 10 in PostgreSQL but the natural logarithm in MySQL: the same SQL gives different results and neither engine warns you.

MOD with negatives deserves a note: in SQL the remainder inherits the dividend's sign, so MOD(-10, 3) is -1, not 2 (it agrees with C and Java, and not with Python). Its practical use is splitting a set into batches:

It's the batched backfill pattern 05-06 recommended: four concurrent processes, each with its own remainder, never overlapping.

The module's classic confusion, resolved by 06-01's distinction:

Three rows. The same query with MAX and MIN would return one, with the most expensive and the cheapest price in the whole catalogue (22.00 and 1.95).

Their most frequent use is clamping a value: GREATEST(stock - 5, 0) never goes below zero and LEAST(discount, 0.30) never exceeds 30 %. A CASE would do the same in four more lines (06-05).

Dialect note: faced with a null, GREATEST(1, NULL) returns 1 in PostgreSQL (it ignores nulls) and NULL in MySQL and Oracle. It's a real and dangerous difference when porting.

3, not 3.33. If both operands are integers, SQL performs integer division and discards the decimal part: it doesn't round, it truncates. It isn't a PostgreSQL whim but the standard —the result's type is derived from the operands', and INTEGER / INTEGER is INTEGER—. The three ways of avoiding it:

(1) One operand with decimals: 10.0 / 3 — the literal 10.0 is NUMERIC and it drags the other one along. (2) An explicit conversion: 10::NUMERIC / 3 or CAST(10 AS NUMERIC) / 3, the only one that works when both operands are integer columns, where you can't "type in a decimal point". (3) Multiplying by 1.0 before dividing: 1.0 * quantity / total, an old trick, portable and legitimate.

The sixteen decimals of the first two columns are the working precision of NUMERIC division: at least 16 significant digits. Compute at that precision and round only when presenting.

"How many units does an order line have on average?" The answer is 113 / 47 units:

SUM(quantity) is BIGINT and so is COUNT(*), so the division is integer and returns 2 instead of 2.4043: a 17 % error, with no warning, in a query that looks obviously correct. AVG doesn't fall into the trap because it returns NUMERIC when the input is an integer; that's the reason it exists.

In 01-04 we said money goes in NUMERIC and never in floating point. Time to prove it.

REAL and DOUBLE PRECISION store numbers in base 2, and 0.1 has no exact representation in base 2, just as 1/3 has none in base 10. The error is tiny… until it accumulates over real data. GreenStore's product revenue —the course's canonical figure— computed both ways:

€727.95 against €727.94. One cent, over 47 lines and 20 orders: the exact sum is 727.9450, which in NUMERIC rounds upwards, while the floating-point accumulation stops at 727.9449999999998 and rounds down. And there's something worse than the cent: the last digits of sum_float8 depend on the order in which the engine adds the rows. A parallel plan, a different index or simply more data can change them, so the same query over the same data can give two different results. With NUMERIC that never happens.

The course's rule, now justified: money in NUMERIC; compute at full precision and round only when presenting. Rounding at each intermediate step introduces accumulation error; computing in floating point introduces a worse one, because it's unpredictable.

Look at (price - cost) / price * 100: the operands are NUMERIC, so there's no integer division. If price and cost were INTEGER (cents, say), that expression would give 0 for all twenty products: it's section 5's error dressed up as a business formula.

Six discounted lines out of the 47, with a total saving of €13.26. Notice line 18: the exact saving is €1.875, which ROUND(…, 2) takes up to 1.88 because NUMERIC rounds half-up; in floating point it would have given 1.87.

Marketing wants to raise prices by 10 % and leave them ending in .95:

FLOOR(x) + 0.95 is the canonical pattern: the integer part plus whatever cents you want. Watch the effect, though: the rice goes from 4.29 to 4.95 (an extra 15 %) and the matcha from 24.20 to 24.95 (3 %). Commercial rounding isn't neutral; measure it before applying it.

Order 1 has three lines (€42.10 of goods) and €4.95 of shipping. How much shipping falls to each line, in proportion to its amount?

2.81 + 1.38 + 0.76 = 4.95. It adds up. But it doesn't always add up: repeat the calculation for order 3 (€4.95 of shipping over €29.53 of goods) and the three rounded shares total €4.96; for order 8, €9.91 instead of €9.90. That leftover cent is unavoidable, because rounding three numbers to two decimals and adding them doesn't have to give the rounded sum. The standard solution is to assign the residue to one line, usually the largest: you compute n − 1 rounded shares and get the last one by subtraction. Here you'd need to compare each line with the maximum of its order, which is a correlated subquery and arrives in 07-02.

The lesson behind it: proportional splitting with rounding always produces residues. If a financial report adds up rounded line items, somebody has to decide where the cent goes. Let your query decide it, not chance.

Two utilities that will come back later.

generate_series doesn't read any table: it manufactures rows out of nothing. That makes it the tool for building reports with no gaps —one row per month even if that month has no sales—, which is what 03-06 announced and what you'll see in 06-03 with dates. RANDOM() is for sampling (ORDER BY RANDOM() LIMIT 10 returns ten rows at random) and for test data; I'm not showing its result because it changes on every run, and for that same reason never use it in a column DEFAULT without thinking twice (05-06: a volatile DEFAULT rewrites the whole table).

The three rows that break the most code when migrating: integer division (a query that works in MySQL returns zeros in PostgreSQL), the base-less logarithm (same SQL, different result, no error) and SQLite's RANDOM(), which returns a huge signed integer and not a decimal between 0 and 1.

• Dividing two integers and expecting decimals. 10 / 3 is 3. Convert one operand to NUMERIC, or use AVG when what you're computing is an average.
• Using ROUND(x, n) over a DOUBLE PRECISION. That function doesn't exist, and the ERROR is warning you about something deeper: you shouldn't have money in floating point.
• Assuming ROUND(2.5) always gives the same thing. 3 in NUMERIC, 2 in DOUBLE PRECISION.
• Storing money in REAL or DOUBLE PRECISION. The error accumulates, the totals stop adding up and the result isn't even reproducible. And don't round at each intermediate step: compute at full precision and round only when presenting.
• Confusing GREATEST/LEAST with MAX/MIN. The first compare columns of one row; the second, rows of one column. And with nulls they behave differently depending on the engine.
• Expecting TRUNC and FLOOR to agree. Only with positives: TRUNC(-1.5) is -1 and FLOOR(-1.5) is -2. And MOD(-10, 3) is -1, not 2.
• Adding up rounded amounts and expecting them to match the total. They don't: somebody has to decide where the residue goes.
• Tip: put the ::NUMERIC as early as possible and the ROUND as late as possible, and always check that the sum of the parts is the total. It's the numeric equivalent of 04-03's "the filter and its negation add up to the total".
• Tip: use MOD(id, n) to split a process into disjoint batches. Simple, deterministic and with no control table.

Management wants a profitability analysis of the catalogue. For each active product, return the price, the cost, the absolute margin, the percentage margin to one decimal and the inventory value (stock * cost) to two decimals. Sort by percentage margin descending and show the top five. Which kind of product dominates the ranking and why?

Over reviews.rating: (1) compute the overall average to 4 decimals and to 0 decimals; (2) compute it again writing SUM(rating) / COUNT(*) — what do you get and why?; (3) compute the average per product rounded to 2 decimals, worst to best. How many products appear and why aren't there 20?

A colleague claims these two queries over order_lines are equivalent:

(1) Predict whether they'll give the same number and run them. (2) Explain how they differ conceptually. (3) Which one matches what the shop really bills, if every invoice prints each line's amount rounded to two decimals?

Solution 1

(The catalogue's average percentage margin is 52.03 %.) The ranking is dominated by cheap hygiene and household products: on a small price, a small cost leaves a high percentage even though the absolute margin is two euros. Percentage margin and absolute margin sort differently: the matcha, with €9.50 of margin, is the one leaving the most money per unit and it isn't here. And product 20 can never come out: it's discontinued and the WHERE active = TRUE excludes it.

Solution 2

1 and 2. AVG(rating) returns 4.0833333333333333: rating is a SMALLINT, but AVG over integers returns NUMERIC. SUM(rating) / COUNT(*), on the other hand, divides 49 by 12 as integers and returns 4. The fact that it matches the rounding to 0 decimals is a coincidence: if the average were 4.9, the integer division would still give 4 and the rounding would give 5. 3.

9 products, not 20: reviews only has 12 rows across 9 distinct products, and a GROUP BY over that table can't invent the 11 nobody has rated. For them to appear you'd have to start from products with a LEFT JOIN (03-03), and their average would be NULL — which isn't 0, and which gets presented with COALESCE (06-04).

Solution 3

1. Both return 727.95, but by luck: GreenStore's 47 line amounts have at most three decimals and the rounding residues cancel out. With other data they wouldn't match. 2. They aren't the same question. A adds first and rounds at the end: it's the mathematically exact amount, rounded a single time, with a maximum error of half a cent, always. B rounds each line and then adds: it introduces up to half a cent of error per line, so with 47 lines the accumulated error can reach about 24 cents and with a million lines, €5,000.

3. And here the brief inverts the answer: if the invoice the customer receives prints each line rounded to two decimals, what the shop actually charges is the sum of those rounded lines, that is, B. In that case B isn't an error, it's the definition of the billed amount, and A would be the figure that doesn't match the paperwork. The moral isn't "always round at the end" but "round where the business rounds": the course's rule applies to analytical reports, while in invoicing per-line rounding is a legal requirement in many countries. What's never acceptable is not knowing which of the two you're computing.

You now compute with judgement:

• You round with ROUND, cut with TRUNC and approximate with CEIL/FLOOR, knowing that with negatives all four part company. You know the .5 trap: NUMERIC rounds half-up and DOUBLE PRECISION half-even, so ROUND(2.5) can be 3 or 2 depending on the type. And ROUND(x, n) doesn't exist for floating point, which is a warning, not a limitation.
• You handle ABS, SIGN, MOD (whose remainder inherits the dividend's sign), POWER, SQRT and the logarithms, with the warning that LOG with no base doesn't mean the same thing in PostgreSQL as in MySQL. And you can tell GREATEST/LEAST —scalar— from MAX/MIN —aggregate—.
• You avoid integer division with a decimal literal, a ::NUMERIC or a 1.0 *, and you can recognise it: SUM(quantity) / COUNT(*) gave 2 where the average is 2.4043. And you've seen the lost cent with real data: €727.95 in NUMERIC against €727.94 in floating point, with the aggravating factor that the floating-point result isn't even reproducible.
• You apply all that to GreenStore: VAT, margins, discounts, commercial rounding to .95 and proportional shipping splits, with the warning that a rounded split doesn't always add up and somebody has to decide where the residue goes.

In the next lesson, date and time functions, comes the data type that answers the most business questions and hides the most errors. You'll see why GreenStore stores everything as DATE and what would change with TIMESTAMP and time zones, the difference between NOW() and CLOCK_TIMESTAMP(), arithmetic with INTERVAL, and at last EXTRACT and DATE_TRUNC, which are the debt 04-05 left outstanding: grouping revenue by month and by quarter without writing twenty conditions by hand. And generate_series will be back, this time so a monthly report shows the months in which nothing was sold.