PolarDB:Numeric types

Precision and scale

Two key terms define NUMERIC column behavior:

• Precision: the total number of significant digits on both sides of the decimal point.

• Scale: the number of digits to the right of the decimal point.

For example, 23.5141 has a precision of 6 and a scale of 4. Integers have a scale of 0.

Column declaration syntax

-- Specify both precision and scale
NUMERIC(precision, scale)

-- Specify precision only; scale defaults to 0
NUMERIC(precision)

-- No constraint; stores any precision and scale up to the type limit
NUMERIC

The maximum explicitly specified precision is 1,000. A column declared without precision or scale accepts values up to the type's internal limit (131,072 digits before the decimal point, 16,383 digits after).

Do not assign unnecessarily high precision to NUMERIC columns. High-precision values consume more storage and can slow query execution.

Insertion behavior

When inserting a value into a NUMERIC(p, s) column:

• If the fractional digits exceed the column's scale, the value is rounded to the specified scale.

• If the fractional digits are fewer than the column's scale, the value is stored as-is (not zero-padded).

• If the digits to the left of the decimal point exceed precision − scale, an error is reported.

The following example shows how three different column definitions handle the same input value:

CREATE TABLE decimals (
    a NUMERIC PRIMARY KEY,
    b NUMERIC(10, 5),
    c NUMERIC
);

INSERT INTO decimals VALUES (
    1.01234567890123456789,
    1.01234567890123456789,
    1.01234567890123456789
);

SELECT * FROM decimals;

           a            |    b    |           c
------------------------+---------+------------------------
 1.01234567890123456789 | 1.01235 | 1.01234567890123456789
(1 row)

Column a and column c store the value exactly. Column b is rounded to 5 decimal places as specified by its scale.

Storage

NUMERIC values are stored without leading or trailing zeros, so declared precision and scale are maximums, not fixed allocations — similar to VARCHAR(n) rather than CHAR(n). Storage cost is 2 bytes per group of 4 decimal digits, plus 3–8 bytes of overhead.

NaN

NUMERIC supports the special value NaN (not a number). Write it as a single-quoted string in SQL:

UPDATE table SET x = 'NaN';

NaN is case-insensitive on input. In PolarDB for PostgreSQL (Compatible with Oracle), NaN values are treated as equal to each other and as greater than all non-NaN values, which allows sorting and use in tree-based indexes.

Rounding behavior

NUMERIC rounds away from zero (half-up). REAL and DOUBLE PRECISION round to the nearest even number (banker's rounding). The following query illustrates the difference:

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) AS x;

  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4
(8 rows)

Ranges and precision

Values outside the supported range cause an error. Values close to zero that cannot be represented as distinct from zero cause an underflow error. Values with higher precision than the type supports are rounded on input.

Output format

By default, floating-point values are output in the shortest precise decimal representation — the closest decimal to the actual stored binary value. The output uses at most 17 significant digits for FLOAT8 and at most 9 for FLOAT4.

Use the extra_float_digits parameter to control output precision:

Set extra_float_digits = 3 for applications that require maximum precision. Set it to 0 for compatibility with older versions of PolarDB for PostgreSQL (Compatible with Oracle).

Special values

Floating-point types support three IEEE 754 special values. Write them as single-quoted strings in SQL:

UPDATE table SET x = 'Infinity';
UPDATE table SET x = '-Infinity';
UPDATE table SET x = 'NaN';

These values are case-insensitive on input. Like NUMERIC, PolarDB for PostgreSQL (Compatible with Oracle) treats NaN floating-point values as equal to each other and as greater than all non-NaN values.

FLOAT(p) syntax

PolarDB for PostgreSQL (Compatible with Oracle) supports the SQL-standard FLOAT and FLOAT(p) notations, where p specifies the minimum acceptable precision in binary digits:

• FLOAT(1) to FLOAT(24) maps to REAL

• FLOAT(25) to FLOAT(53) maps to DOUBLE PRECISION

• FLOAT with no precision specified is treated as DOUBLE PRECISION

Values of p outside the range 1–53 cause an error.

Type aliases

Use BIGSERIAL when your table will have more than 2,147,483,647 rows over its lifetime.

Gaps may appear in serial sequences even without deleted rows. A sequence value is consumed when assigned, even if the transaction that triggered the assignment is later rolled back.

Alternatively, use the SQL-standard identity column feature described in the CREATE TABLE topic.