Decimal
The Decimal type is useful for applications that require exact decimal representations, such as financial calculations or scientific computations.
We can use DECIMAL(P, S)
to indicate decimal types.
P
is the precision, which is the total number of digits in the number, it's range is [1, 76].S
is the scale, which is the number of digits to the right of the decimal point, it's range is [0, P].
If P
is less than 38, the physical datatype of decimal is Decimal128
, otherwise it's Decimal256
.
For a DECIMAL(P, S) data type:
 The minimum value is
10^P + 1
divided by10^S
.  The maximum value is
10^P  1
divided by10^S
.
If you have a DECIMAL(10, 2)
, you can store values with up to 10 digits
, with 2 digits
to the right of the decimal point. The minimum value is 9999999.99
, and the maximum value is 9999999.99
.
Examples
 Create a table with decimal data type.
CREATE TABLE decimal (
value DECIMAL(36, 18)
);
 Insert two values.
INSERT INTO decimal
VALUES
(0.152587668674722117),
(0.017820781941443176);
 Select all values from the table.
SELECT * FROM decimal;
Result:
┌────────────────────────────┐
│ value │
├────────────────────────────┤
│ 0.152587668674722117 │
│ 0.017820781941443176 │
└────────────────────────────┘
Precision Inference
DECIMAL has a set of complex rules for precision inference. Different rules will be applied for different expressions to infer the precision.
Arithmetic Operations

Addition/Subtraction:
DECIMAL(a, b) + DECIMAL(x, y) > DECIMAL(max(a  b, x  y) + max(b, y) + 1, max(b, y))
, which means both integer and decimal parts use the larger value of the two operands. 
Multiplication:
DECIMAL(a, b) * DECIMAL(x, y) > DECIMAL(a + x, b + y)
. 
Division:
DECIMAL(a, b) / DECIMAL(x, y) > DECIMAL(a + y, b)
.
Comparison Operations
 Decimal can be compared with other numeric types.
 Decimal can be compared with other decimal types.
Aggregate Operations
 SUM:
SUM(DECIMAL(a, b)) > DECIMAL(MAX, b)
 AVG:
AVG(DECIMAL(a, b)) > DECIMAL(MAX, max(b, 4))
where MAX
is 38 for decimal128 and 76 for decimal256.
Adjusting Result Precision
Different users have different precision requirements for DECIMAL. The above rules are the default behavior of databend. If users have different precision requirements, they can adjust the precision in the following ways:
If the expected result precision is higher than the default precision, adjust the input precision to adjust the result precision. For example, if the user expects to compute AVG(col) to get DECIMAL(x, y) as the result, where col is of type DECIMAL(a, b), the expression can be rewritten as cast(AVG(col) as Decimal(x, y)
or AVG(col)::Decimal(x, y)
.
Note that in the conversion or calculation of decimal types, if the integer part overflows, an error will be thrown, and if the precision of the decimal part overflows, it will be directly discarded instead of being rounded.