FIXNUMERIC Decimal Type Overview - AnalyticDB for PostgreSQL

FIXNUMERIC is a fixed-precision decimal data type for AnalyticDB for PostgreSQL that stores decimal values as native integers (INT64 or INT128) with a preset scale. By mapping decimals to integers, FIXNUMERIC avoids the overhead of variable-precision arithmetic and the precision loss caused by binary representation errors in floating-point calculations. In compute-intensive workloads such as large-scale aggregations and financial calculations, FIXNUMERIC significantly outperforms the native NUMERIC and DECIMAL types.

When to use `FIXNUMERIC`

Use FIXNUMERIC when your workload requires all of the following:

Exact decimal values — financial calculations, ledgers, billing, or compliance reporting where rounding errors are unacceptable
High query performance — large-scale SUM, AVG, or other aggregations on numeric columns
Predictable data ranges — values that fit within the 64-bit or 128-bit integer range

Stick with NUMERIC if you need:

NaN support — FIXNUMERIC does not support NaN values
Unlimited range — values that exceed the fixed 64-bit or 128-bit integer bounds
Variable scale — columns where the number of decimal places differs across rows

How `FIXNUMERIC` works

FIXNUMERIC maps each decimal value to an integer by multiplying by a power of 10 equal to the preset scale, then storing the result in a native integer register. For example, 3.141592 with scale 6 is stored as the integer 3141592. Arithmetic operations run directly on these integers using native CPU instruction sets, which is why aggregate queries over FIXNUMERIC columns are substantially faster than equivalent queries over NUMERIC columns.

FIXNUMERIC comes in two variants. Choose based on the precision and range your data requires:

Variant	Storage	Precision	Equivalent type	Best for
`FIXNUMERIC`	64-bit integer (`INT64`)	Up to 19 digits, 6 decimal places	`NUMERIC(19,6)`	Small and medium-scale calculations
`FIXNUMERIC128`	128-bit integer (`INT128`)	Up to 38 digits, 10 decimal places	`NUMERIC(38,10)`	Large-range, high-precision analysis

Prefer FIXNUMERIC over FIXNUMERIC128 unless your values require more than 19 significant digits or more than 6 decimal places.

Comparison with `NUMERIC`

Feature	`FIXNUMERIC` / `FIXNUMERIC128`	`NUMERIC`
Precision	Fixed (6 or 10 decimal places by default)	Variable
Range	Limited (based on 64-bit or 128-bit integers)	Almost unlimited
Performance	High (through operations based on native CPU instruction sets)	Low
Rounding method	Round toward zero	Round to nearest
NaN support	Not supported	Supported

Type conversion in arithmetic operations

Some functions convert FIXNUMERIC values to DOUBLE before computing a result. This applies to round, sqrt, exp, ln, pow, power, log, and log10. The output of these functions is a floating-point value, not a FIXNUMERIC. For operations that must return exact decimal results, use trunc, abs, ceil, floor, or the standard arithmetic operators instead.

Limitations

Only AnalyticDB for PostgreSQL V7.0 instances running V7.2.1.1 or later support the fixnumeric extension. To check your minor version, see View the minor version of an instance. To upgrade, see UpgradeDBVersion.
Instances in Serverless mode do not support the fixnumeric extension.
FIXNUMERIC and FIXNUMERIC128 have fixed value ranges. Inserting a value that exceeds the range causes an overflow error.
The scale is fixed at the type level, not the column level. You do not specify the scale when creating a column — the scale is preset at 6 for FIXNUMERIC and 10 for FIXNUMERIC128.

Install the extension

The fixnumeric extension does not support GUI-based installation. To install it, submit a ticket. After installation, restart your AnalyticDB for PostgreSQL instance. To uninstall the extension, submit a ticket.

Use `FIXNUMERIC` in queries

FIXNUMERIC works like any other numeric data type and can replace FLOAT or DOUBLE PRECISION columns that require exact values.

Step 1: Create a table and insert data

Create a table with FIXNUMERIC and FIXNUMERIC128 columns alongside FLOAT and NUMERIC columns to compare behavior:

CREATE TABLE test_t(a float, b numeric(14,6), c fixnumeric, d fixnumeric128);
INSERT INTO test_t SELECT v+0.1,v+0.1,v+0.1,v+0.1 FROM generate_series(1,1000) v;

Step 2: Create indexes

FIXNUMERIC supports indexes. Create indexes on the FIXNUMERIC columns:

CREATE INDEX ON test_t (c);
CREATE INDEX ON test_t (d);

Step 3: Run aggregation queries

Query the SUM of each column. The FLOAT column returns an approximate value due to binary representation errors, while NUMERIC, FIXNUMERIC, and FIXNUMERIC128 all return exact values:

SELECT SUM(a), SUM(b), SUM(c), SUM(d) FROM test_t;

sum         |      sum      |        sum        |        sum
--------------------+---------------+-------------------+-------------------
 500600.00000000175 | 500600.000000 | 500600.0000000000 | 500600.0000000000
(1 row)

Query the AVG of each column. The same precision difference appears:

SELECT AVG(a), AVG(b), AVG(c), AVG(d) FROM test_t;

avg        |         avg          |    avg     |      avg
-------------------+----------------------+------------+----------------
 500.6000000000017 | 500.6000000000000000 | 500.600000 | 500.6000000000
(1 row)

Supported functions

FIXNUMERIC is compatible with most functions that support NUMERIC or DECIMAL types, including all aggregate functions. Functions that return a floating-point result are marked with an asterisk.

Function	Description
`sum`, `avg`, `min`, `max`	Calculates the sum, average, minimum, or maximum value
`in_range`	Checks whether a value falls within a range
`abs`	Returns an absolute value
`sign`	Returns the sign of a value: `-1`, `0`, or `+1`
`trunc`	Truncates a number to a specific number of decimal places
`ceil`	Rounds up to the nearest integer
`ceiling`	Alias for `ceil`
`floor`	Rounds down to the nearest integer
`mod`	Returns the remainder of a division
`scale`	Returns the number of decimal places
`add`, `sub`, `mul`, `div`	Addition, subtraction, multiplication, and division
`=`, `>`, `<`, `<=`, `>=`, `<>`	Comparison operators
`round`*	Rounds to the nearest integer; returns `DOUBLE`
`sqrt`*	Returns the square root; returns `DOUBLE`
`exp`*	Returns the natural exponent; returns `DOUBLE`
`ln`*	Returns the natural logarithm; returns `DOUBLE`
`pow`*	Raises to a power; returns `DOUBLE`
`power`*	Alias for `pow`; returns `DOUBLE`
`log`*	Logarithmic operation; returns `DOUBLE`
`log10`*	Base-10 logarithm; returns `DOUBLE`
`to_char`	Converts to a string; `FIXNUMERIC` is cast to `NUMERIC` first

*These functions convert FIXNUMERIC to DOUBLE before computing. The result is a floating-point value.

When to use FIXNUMERIC

How FIXNUMERIC works

Comparison with NUMERIC