Decimal ​

Decimal values represent exact fixed-point numbers. They preserve decimal digits during addition, subtraction and multiplication. Division discards excess fractional digits instead of rounding them.

Use Decimal for amounts, prices and other values that require an exact decimal scale.

Syntax ​

Decimal(P, S)
Decimal(P)
Decimal
Decimal32(S)
Decimal64(S)
Decimal128(S)
Decimal256(S)
NUMERIC(P, S)

Parameters ​

• P — Precision: the total number of decimal digits, from 1 through 76.
• S — Scale: the number of fractional digits, from 0 through P.

Decimal(P) means Decimal(P, 0). Bare Decimal and bare NUMERIC mean Decimal(10, 0). Use LEGACY_NUMERIC for SQLite numeric affinity.

Decimal Value Ranges ​

The coefficient must contain at most P digits. Values lie strictly between -10^(P-S) and 10^(P-S), in steps of 10^-S. For example, Decimal(5, 2) accepts values from -999.99 through 999.99.

The range is checked during casts, table writes and arithmetic. Overflow raises NUMERIC_OVERFLOW for every supported precision.

Operations and Result Type ​

Division truncates excess fractional digits toward zero. Decimal division and remainder by zero raise INVALID_ARGUMENT.

Cast approximate floating-point values explicitly before combining them with Decimal. A decimal literal supplied as text avoids an initial binary floating-point approximation. Statistical functions may return an approximate result even when their input is Decimal; consult the function reference.

Examples ​

Exact addition ​

SELECT CAST('1.20' AS Decimal(18, 2))
     + CAST('2.40' AS Decimal(18, 2)) AS amount
FORMAT csv;

3.60

Division without rounding ​

SELECT CAST('2.0000' AS Decimal(18, 4)) / 3,
       CAST('-2.0000' AS Decimal(18, 4)) / 3
FORMAT csv;

0.6666,-0.6666

Storing prices ​

CREATE TABLE products(id UInt32, price Decimal(18, 2));
INSERT INTO products VALUES (1, '19.95'), (2, '5.05');

SELECT sum(price) AS total_price
FROM products
FORMAT csv;

25.00