DECIMAL precision and scale

Concepts covered: Numeric Precision

A DOUBLE stores a number as a binary fraction in 64 bits. Binary fractions represent a half or an eighth exactly, but nothing whose denominator has a factor of 5, so 0.1 is stored as the nearest value the format can reach, which begins 0.1000000000000000055511151231257827. The error sits far past the digits anyone looks at, which is how it stays hidden until a ledger that adds millions of such values comes out a few cents off. DECIMAL stores digits, the way a bank statement does, so 0.1 means 0.1.

Precision and scale

DECIMAL takes 2 parameters. Precision is the total count of digits the value can hold, and scale is how many of those sit to the right of the decimal point. DECIMAL(10,2) holds up to 8 digits before the point and 2 after, so its largest value is 99999999.99. Scale is fixed: 5 stored in DECIMAL(10,2) is 5.00, and a value with more fractional digits is rounded to fit. Precision is a hard ceiling: 123456789.00 needs 9 digits before the point, and casting it to DECIMAL(10,2) fails with an error rather than silently dropping a digit.

• 38
• 18

The sum that comes out wrong

The pennies table stores 3 pairs of amounts as text, the way a CSV loader would leave them. The query adds each pair twice, once after casting to DOUBLE and once after casting to DECIMAL(10,2). Predict the first row before running.

SELECT
  label,
  CAST(a AS DOUBLE) + CAST(b AS DOUBLE) AS double_sum,
  CAST(a AS DECIMAL(10,2)) + CAST(b AS DECIMAL(10,2)) AS decimal_sum
FROM pennies
ORDER BY label

The DOUBLE column shows 0.30000000000000004, 3.3000000000000003 and 0.7999999999999999. Each is the binary sum of 2 approximations, printed with every digit the format carries. The DECIMAL column shows 0.30, 3.30 and 0.80. Its type is DECIMAL(11,2): adding 2 values of the same scale keeps that scale and adds 1 digit of precision for a possible carry.

What arithmetic does to the type

Every operation on DECIMAL values produces a DECIMAL whose precision and scale follow from the inputs. Multiplication adds both: DECIMAL(10,2) times DECIMAL(6,4) gives DECIMAL(16,6), because a 2-place number times a 4-place number can need 6 places to stay exact. An INTEGER in a DECIMAL expression is treated as a DECIMAL with scale 0. The engine never rounds during the chain, so the result holds every digit, and you decide where to round by casting the finished value to the scale the report needs. Casting to a smaller scale rounds half away from zero.

order_lines stores unit_price and tax_rate as text and quantity as a whole number. The CTE casts each column once, and the outer query builds the subtotal, the raw tax with its full 6 places, and the tax rounded to cents.

WITH typed AS (
  SELECT
    line_id,
    CAST(unit_price AS DECIMAL(10,2)) AS unit_price,
    CAST(quantity AS DECIMAL(5,0)) AS quantity,
    CAST(tax_rate AS DECIMAL(6,4)) AS tax_rate
  FROM order_lines
)
SELECT
  line_id,
  unit_price * quantity AS subtotal,
  unit_price * quantity * tax_rate AS tax_raw,
  CAST(unit_price * quantity * tax_rate AS DECIMAL(12,2)) AS tax
FROM typed
ORDER BY line_id

Line 1 shows the chain: 19.99 times 3 is 59.97 in DECIMAL(15,2), times 0.0825 is 4.947525 in DECIMAL(21,6), and the final cast rounds it to 4.95. Line 2 holds 0.742500 and rounds down to 0.74, since the dropped digits start with 2. Precision only grows along a chain, and the engine caps it at 38. Once a derived precision would pass 38, the result type is clamped there, and the query keeps running until it reaches a row whose value no longer fits, which raises an overflow error at that row. A query that passes on test data can fail on 1 large row in production. The repair is to cast an intermediate result back down to the scale it needs, and to do it at the point where the extra digits stop meaning anything.

A column price is DECIMAL(6,3) and qty is DECIMAL(4,1). What is the type of price * qty?

• DECIMAL(6,3), because the result takes the wider input's type
• DECIMAL(10,3), because precision adds and scale follows the wider input
• DECIMAL(24,3), because precision multiplies and scale follows the wider input
• DECIMAL(10,4), because multiplication adds both precisions and both scales

Use DECIMAL

• Prices and tax rates
• Exchange rates and fees
• Anything summed or reconciled
• Values compared for equality

Use DOUBLE

• Sensor readings and coordinates
• Statistical measures
• Ratios shown to 2 or 3 places
• Very large or very small magnitudes

About this lesson

This section is part of the Data Types: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.