SQL Numeric Types

Lesson 2.2 · Numbers and calculations treated every number alike. They aren't. SQL has three families of numeric type, and each stores a number differently:

• Integers (INTEGER, BIGINT) hold whole numbers exactly.
• Decimals (DECIMAL, also called NUMERIC) hold a fixed number of decimal places exactly. Money belongs here.
• Floating point (DOUBLE, FLOAT) holds a very wide range of values, fast, but only approximately.

typeof() tells you which one a value is:

select typeof(price) as price_type,
       typeof(quantity_on_hand) as quantity_type
from products
limit 1

That returns DOUBLE and BIGINT. The workbench loads each table from a CSV file, and DuckDB picks a type for every column from the text it reads. It chose a floating point type for price, even though the shop's own schema file declares it DECIMAL(12,2). Data you import yourself gets the same treatment, so it's worth checking.

Floating point is close, not exact

Add 8% sales tax to one plant:

select product_name, price, price * 1.08 as with_tax
from products
where product_name = 'Monstera Deliciosa'

$28 plus 8% is $30.24, but the result is 30.240000000000002. A DOUBLE stores numbers in binary, and most decimal fractions, 1.08 among them, have no exact binary form. The tiny error usually hides in the 16th digit. It surfaces in a price list like this one, and in totals that should agree to the cent but don't.

DECIMAL(p, s): exact, to a fixed number of places

A decimal type takes two numbers. Precision is the total count of digits, and scale is how many of them come after the decimal point. So DECIMAL(10,2) holds up to eight digits before the point and exactly two after it. The way you write a number gives it a type, too:

select typeof(7) as whole,
       typeof(7.50) as with_point,
       typeof(7.5e0) as scientific

7 is an INTEGER, 7.50 is a DECIMAL(3,2), and the scientific form 7.5e0 is a DOUBLE.

To change a value's type, cast it: cast(value as decimal(10,2)). DuckDB and PostgreSQL also accept the shorthand value::decimal(10,2). A cast to a decimal rounds to the scale:

select cast(123.456 as decimal(5,2)) as fits

That gives 123.46. A value with more digits before the point than the precision allows doesn't round. It fails:

select cast(12345.6 as decimal(5,2)) as too_big

DuckDB stops with Conversion Error: Casting value "12345.6" to type DECIMAL(5,2) failed: value is out of range! rather than return a wrong number. Here is the difference exactness makes:

select 0.1 + 0.2 = 0.3 as exact_decimals,
       0.1::double + 0.2::double = 0.3::double as floating_point

As decimals, 0.1 + 0.2 = 0.3 is true. As doubles it's false. Never compare floating point values with =.

Whole numbers and division

select 7 / 2 as slash,
       7 // 2 as whole_division,
       7 % 2 as remainder

In DuckDB, / always gives a fractional answer: 7 / 2 is 3.5, a DOUBLE. The DuckDB operator // divides and drops the fraction, giving 3, and % gives the remainder, 1. PostgreSQL, SQL Server, and SQLite answer 3 for 7 / 2, because dividing two integers there gives an integer. The integer division mistake shows the percentages that breaks.

An INTEGER holds values up to 2,147,483,647, and a BIGINT up to about 9.2 quintillion. Go one past the limit and DuckDB refuses rather than wrap around to a negative number:

select 2147483647::integer + 1 as overflow

The error is Out of Range Error: Overflow in addition of INT32 (2147483647 + 1)!. Cast one side to bigint and the sum fits.