Use FIXNUMERIC data type - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL supports the FIXNUMERIC data type, which combines fixed precision with integer-based storage. In compute-intensive scenarios, the FIXNUMERIC type significantly outperforms native NUMERIC or DECIMAL types, delivering better performance for aggregation and basic arithmetic operations while maintaining lossless precision within specific ranges.

Introduction

FIXNUMERIC is a fixed-precision decimal data type in AnalyticDB for PostgreSQL, designed to provide better performance and more efficient storage than native NUMERIC or DECIMAL types. As a high-performance subset of NUMERIC or DECIMAL types, FIXNUMERIC maps decimals to integers by using integer-based storage mechanisms (INT 64 and INT 128) and a preset scale (the number of decimal places). The FIXNUMERIC type prevents the overheads of simulating decimal calculations for traditional NUMERIC or DECIMAL types and the precision loss caused by binary representation errors in floating-point calculations. The FIXNUMERIC type is suitable for scenarios that require high-precision calculations, controllable data ranges, high performance, and large disk space.

The FIXNUMERIC type has two modes:

FIXNUMERIC: implemented based on 64-bit integers with a fixed precision of (19,6). It supports up to 19 digits that include 6 decimal places, equivalent to NUMERIC(19,6). FIXNUMERIC is suitable for small and medium-scale data calculation scenarios to balance performance and storage efficiency.
FIXNUMERIC128: implemented based on 128-bit integers with a fixed precision of (38,10). It supports up to 38 digits that include 10 decimal places, equivalent to NUMERIC(38,10). FIXNUMERIC128 is suitable for scenarios that require large-range and high-precision data calculations and excels particularly in large-scale data analysis scenarios.

Comparison with the NUMERIC type

Feature	FIXNUMERIC or 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

Limits

Only AnalyticDB for PostgreSQL V7.0 instances of V7.2.1.1 or later support the fixnumeric extension.
Note
For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor version of an instance. If your instance does not meet the preceding requirements, update the minor version of the instance. For more information, see UpgradeDBVersion.
AnalyticDB for PostgreSQL instances in Serverless mode do not support the fixnumeric extension.
FIXNUMERIC and FIXNUMERIC128 types have fixed value ranges. When these value ranges are exceeded, an overflow error occurs.
Unlike native DECIMAL or NUMERIC types, the FIXNUMERIC type has a fixed scale when columns are created. You do not need to explicitly define the scale when you create a table because the scale is preset at the underlying level.

Installation and uninstallation

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

Examples

The FIXNUMERIC type can be used like other data types in AnalyticDB for PostgreSQL and can seamlessly replace floating-point types, such as FLOAT and DOUBLE PRECISION.

Step 1: Create a table and write data to the table

Create a table named test_t with the FIXNUMERIC data type and write test data to the table.

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

The FIXNUMERIC type supports indexes. Create indexes for the test_t table.

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

Step 3: Query data

Query the SUM values of the corresponding columns.

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

The FLOAT type returns a less accurate approximate value. The NUMERIC, FIXNUMERIC, and FIXNUMERIC128 types return exact values.

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

Query the AVG values of the corresponding columns.

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

The FLOAT type returns an approximate value. The NUMERIC, FIXNUMERIC, and FIXNUMERIC128 types return exact values.

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

Supported functions

The FIXNUMERIC type provides high compatibility and scalability. It supports all aggregate functions, such as SUM, AVG, MIN, and MAX, and is compatible with most functions that support native DECIMAL or NUMERIC types. The following table describes the supported functions.

Function name	Description
`sum`, `avg`, `min`, or `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 parameter value, such as -1, 0, or +1.
`round`	Rounds a number to the nearest integer with the `FIXNUMERIC` type converted to the `DOUBLE` type.
`trunc`	Truncates a number to a specific number of decimal places.
`ceil`	Rounds up a number to the nearest integer.
`ceiling`	Rounds up a number to the nearest integer (alias for `ceil`).
`floor`	Rounds down a number to the nearest integer.
`add`, `sub`, `mul`, or `div`	Performs addition, subtraction, multiplication, or division.
`=`, `>`, `<`, `<=`, `>=`, or `<>`	Acts as a comparison operator, such as equal to, greater than, less than, less than or equal to, greater than or equal to, or not equal to.
`mod`	Returns a remainder.
`sqrt`	Returns a square root with the `FIXNUMERIC` type converted to the `DOUBLE` type.
`exp`	Returns a natural exponent with the `FIXNUMERIC` type converted to the `DOUBLE` type.
`ln`	Returns a natural logarithm with the `FIXNUMERIC` type converted to the `DOUBLE` type.
`pow`	Performs a power operation with the `FIXNUMERIC` type converted to the `DOUBLE` type.
`power`	Performs a power operation with the `FIXNUMERIC` type converted to the `DOUBLE` type (alias for `pow`).
`scale`	Returns the number of decimal places.
`log`	Performs a logarithmic operation with the `FIXNUMERIC` type converted to the `DOUBLE` type.
`log10`	Performs a base-10 logarithmic operation with the `FIXNUMERIC` type converted to the `DOUBLE` type.
`to_char`	Converts a number to a string with the `FIXNUMERIC` type converted to the `NUMERIC` type.