skip to content

In SQL, what precision and scale does DECIMAL arithmetic return, and why does 1.0 / 3 show different digits on each engine?

level: middleimportance: nice to knowfreq 30%

answer

  1. the operation decides the result scale
  2. addition keeps the larger scale
  3. multiplication adds scales
  4. division has no exact answer
  5. multiply before you divide

basics

~20 s

Addition keeps the larger operand scale and multiplication adds the scales on most engines. Division is often inexact, so each engine picks its own result scale, and 1.0 / 3 can show 20, 5 or 6 decimals.

solid answer

~40 s

A result's scale comes from the operation, not from any column you later store it in. On PostgreSQL, MySQL and SQL Server, `+` and `-` keep the larger operand scale, so `1.10 + 2.5` is 3.60, and `*` adds the scales, so `1.10 * 1.10` is 1.2100. Division cannot generally be exact, so the engine chooses: PostgreSQL gives `1.0 / 3` 20 decimal digits, MySQL adds its `div_precision_increment` (default 4) to the first operand's scale and shows 0.33333, and SQL Server shows 0.333333. The practical rules are to multiply before dividing, and to pin the final value with `ROUND` or a `CAST`.

code

sql · 6 lines
sql
SELECT 1.10 + 2.5;         -- 3.60
SELECT 1.10 * 1.10;        -- 1.2100
SELECT 1.0 / 3;            -- 0.33333333333333333333
SELECT 10.00 / 3 * 3;      -- 9.9999999999999999
SELECT 10.00 * 3 / 3;      -- 10.0000000000000000
SELECT scale(1.10 * 1.10); -- 4

go deeper

for a junior

Recall that dividing or multiplying decimals can return more digits than you started with, and that ROUND sets how many you show.

for a middle

State the scale rule for each operation and predict 1.10 * 1.10 by hand. Explain that division is engine-defined, using 1.0 / 3 on two named engines as the example.

for a senior

Diagnose missing or surplus digits by following scale through the expression, multiply before dividing, and watch for engines that reduce scale when operands are declared much wider than their data.

for a principal

Set a convention for where a calculation's final scale is pinned and how wide intermediates may be, so that reports agree across engines and a port does not change the digits without anyone noticing.

## Precision and scale in one paragraph For an exact number, **precision** is the total count of significant digits and **scale** is the count of digits after the decimal point: 123.45 has precision 5 and scale 2. Declaring those limits on a column belongs with schema design. This question is about the value an *expression* returns, and that follows rules of its own. A declared `DECIMAL(10,2)` column does not constrain what `price * rate` returns until you store or cast the result. ## Addition, subtraction and multiplication These three can always be exact, so engines can give them simple rules. PostgreSQL, MySQL and SQL Server agree on the scale: | Operation | Result scale | Example | Result | |---|---|---|---| | `+` and `-` | the larger operand scale | `1.10 + 2.5` | 3.60 | | `*` | the sum of the operand scales | `1.10 * 1.10` | 1.2100 | Two consequences follow: - **Scale grows through a chain of multiplications.** Multiplying scale-2 values four times gives scale 8. That is correct, but it surprises a report that expected two decimals. - **Trailing zeros are information about the type, not about accuracy.** `1.2100` and `1.21` are equal numerically, and comparing them returns true. Oracle's `NUMBER` works differently: it does not carry a fixed scale through expressions, so `1.10 * 1.10` simply shows 1.21. SQLite has no exact decimal storage class at all, so these rules do not apply to it. ## Division A quotient such as 1 / 3 has no finite decimal form, so an engine has to stop somewhere, and the stopping point is the engine's own decision. | Engine | `1.0 / 3` | How the scale is chosen | |---|---|---| | PostgreSQL | 0.33333333333333333333 | enough for about 16 significant digits, at least the operand scales | | MySQL | 0.33333 | first operand's scale plus `div_precision_increment`, default 4 | | SQL Server | 0.333333 | a documented formula from both operands' precision and scale | This explains output that looks strange at first. PostgreSQL's reference lists `5.0 / 2` as `2.5000000000000000`, a value that divides exactly and is still padded out to the chosen scale. MySQL's `1 / 3` shows 0.3333 while its `1.0 / 3` shows 0.33333, because the first operand's scale counts toward the result. ## Where a limit bites - **PostgreSQL** expressions are effectively unconstrained up to very large limits, so addition and multiplication stay exact and the risk there is excess digits. Its division still stops at the chosen scale. - **SQL Server** caps a decimal at precision 38. When a formula's result would exceed that, it reduces the scale, and in that case can be left with as few as about 6 decimal digits. Multiplying two `DECIMAL(38,10)` values does not give scale 20, and an operand declared far wider than its data can lose digits it did not need to lose. - **MySQL's** division scale means a first operand with a small scale displays few decimals. `div_precision_increment` is also a setting, so the digit count can differ between servers. ## Getting a predictable number 1. **Multiply before dividing.** In PostgreSQL, `10.00 / 3 * 3` is 9.9999999999999999, because the quotient was cut at its chosen scale before the multiplication. `10.00 * 3 / 3` returns 10.0000000000000000. A pro-rata share belongs in the form `amount * part / whole`. 2. **Pin the final value once.** `ROUND(expr, 2)` or `CAST(expr AS DECIMAL(12,2))` sets the scale you intend. Round only at the agreed stage, since rounding an intermediate value throws away digits the final answer needs. 3. **Size wide enough but not absurdly wide.** On SQL Server, an operand far wider than its data can trigger the scale reduction above, so match declared sizes to real values. 4. **Check rather than guess.** PostgreSQL's `scale(x)` returns the scale of a value, so `scale(1.10 + 2.5)` is 2, and `trim_scale(8.4100)` removes trailing zeros, giving 8.41. ## What to say in an interview Separate the operations. The exact three follow rules you can predict by hand on the engines above. Division is the one to treat as engine-defined, so state which engine you are predicting and avoid relying on the digit count across a port. Whichever engine you name, say that the final scale should be set by the query rather than inherited.

  • Does a column's DECIMAL(10,2) declaration limit what price * rate returns in a query?
    No. The multiplication's result scale comes from the operands, so two scale-2 values give scale 4, whatever columns they came from. The declared limit applies only when the value is cast or stored in such a column. At that point PostgreSQL rounds to the declared scale and raises an error if the integer part does not fit.
  • Why can SQL Server return fewer decimals from a calculation on very wide DECIMAL columns?
    Its result type has a maximum precision of 38. When the formula's result would exceed that, SQL Server reduces the scale to protect the integer digits, potentially to about 6 decimals. Declaring operands as wide as `DECIMAL(38,10)` makes this more likely, so sizing them to the real data keeps more usable decimals.
  • How do you check what scale an expression returned in PostgreSQL?
    Call `scale(x)`, which returns the count of digits in the fractional part: `scale(8.4100)` is 4. `min_scale(8.4100)` returns 2, the fewest digits needed to represent the value precisely, and `trim_scale(8.4100)` returns 8.41 without trailing zeros.

  • DECIMAL(10,2) times DECIMAL(10,2) always stays at scale 2.
  • Division on DECIMAL is exact, so every engine prints the same digits.
  • Multiplying back after dividing always restores the original number.
  • Trailing zeros in 2.5000000000000000 show the engine lost precision.
  • A column's declared scale limits every expression that reads the column.