PolarDB:Numeric types

Numeric types

Integer types

The SMALLINT, INTEGER, and BIGINT types store integers of various ranges. An error occurs if the types attempt to store values outside the allowed value range.

The INTEGER type is commonly used because it offers the best balance among value range, storage size, and performance. In most cases, the SMALLINT type is used only if the disk space is insufficient. The BIGINT type is designed for scenarios in which the value range of the INTEGER type is insufficient.

SQL specifies only the integer types INTEGER (or INT), SMALLINT, and BIGINT. The INT2, INT4, and INT8 types are extensions, which are also used in many other SQL database systems.

Arbitrary precision numbers

The NUMERIC type can store numbers with a large number of digits. We recommend that you use the NUMERIC type to store monetary amounts and other quantities that require exactness. Calculations such as addition, subtraction, and multiplication of NUMERIC values generate exact results where possible. However, calculations of NUMERIC values are slower than calculations of values of the integer types or the floating-point types that are described in the "Floating-point types" section of this topic.

In the following text, two terms are introduced: precision and scale. The precision of a NUMERIC value specifies the total number of significant digits in the whole number, which is the number of digits to both sides of the decimal point. The scale of a NUMERIC value specifies the number of decimal digits in the fractional part to the right of the decimal point. Based on the terms, the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

You can specify the maximum precision and the maximum scale of a NUMERIC column. To declare a NUMERIC column, use the following syntax:

    NUMERIC(precision, scale)

The precision must be a positive integer, and the scale can be a positive integer or 0. Alternatively, you can use the following syntax:

    NUMERIC(precision)

This is equivalent to specifying a scale of 0. Alternatively, you can use the following syntax to create a NUMERIC column without a precision or scale:

    NUMERIC

The created column can store numeric values of any precision and scale within the upper limit of precision. Such a column does not convert input values to values of a specific scale. In contrast, NUMERIC columns for which a scale is specified convert input values to values of the specified scale. The SQL standard requires a default scale of 0, which indicates that the input values are converted to values of the integer precision. This is not useful. If you are concerned about portability, always explicitly specify the precision and scale of a NUMERIC column.

If the scale of a value to be stored is greater than the specified scale of the column, the system rounds the value to the specified number of fractional digits. If the number of digits to the left of the decimal point exceeds the specified precision minus the specified scale, an error is reported.

Numeric values are physically stored without leading zeros or trailing zeros. Therefore, the specified precision and scale of the column are both maximums rather than fixed allocations. In this sense, the NUMERIC type is more similar to the VARCHAR(``N``) type than to the CHAR(``N``) type. The actual storage requirement is two bytes for each group of four decimal digits, plus an overhead of three to eight bytes.

In addition to regular numeric values, the NUMERIC type supports special type NaN, which indicates not a number. An operation on a NaN input value generates another NaN value. If you write NaN values as constants in an SQL statement, you must enclose the values with single quotation marks ('). Example: UPDATE table SET x = 'NaN'. When you enter NaN values, the NaN values are recognized as not case-sensitive.

The DECIMAL and NUMERIC types are equivalent. Both types are part of the SQL standard.

During rounding, the values of the NUMERIC type are rounded to integers that are away from zero. On most machines, the values of REAL and DOUBLE PRECISION types are rounded to the nearest even numbers. The following sample code provides an example on how the values of the NUMERIC and DOUBLE PRECISION types are rounded:

    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)

Floating-point types

The REAL and DOUBLE PRECISION types are inexact and variable-precision numeric types. On all supported platforms, the REAL and DOUBLE PRECISION types are implementations of the Institute of Electrical and Electronics Engineers (IEEE) 754 standard for Binary Floating-Point Arithmetic, which correspond to single precision and double precision, to the extent that the underlying processor, operating system, and compiler support the standard.

Inexact means that some values cannot be exactly converted to the internal format and are stored as approximations. Therefore, discrepancies may occur when values of such types are stored and retrieved. How to process these errors and how they propagate based on calculations are the subject of an entire branch of mathematics and computer science and are not described in this topic. This topic describes the following items:

• If you require exact storage and calculations such as calculations on monetary amounts, use the NUMERIC type.

• If you want to implement complicated calculations by using these types for important business, especially if you rely on specific behaviors in boundary cases such as infinity and underflow, you must carefully evaluate your implementation.

• Comparing two floating-point values for equality cannot always generate the results that you expect.

On all supported platforms, the REAL type supports a range of 1E-37 to 1E+37 with a precision of at least six decimal digits. The DOUBLE PRECISION type supports a range of 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are excessively large or small cause an error. An input number may be rounded if the precision of the input number is high. Numbers that are close to zero and are not representable as distinct from zero cause an underflow error.

By default, floating-point values are generated in the text format in their shortest precise decimal representation. The generated decimal value is closer to the real stored binary value than to any other value representable in the same binary precision. However, the output value is never exactly midway between two representable values. This prevents a widespread bug in which input routines do not properly follow the round-to-nearest-even rule. The value uses at most 17 significant decimal digits for FLOAT8 values, and at most 9 digits for FLOAT4 values.

For compatibility with the output values generated by earlier versions of PolarDB for PostgreSQL (Compatible with Oracle), and to allow the output value precision to be reduced, you can use the extra_float_digits parameter to specify rounded decimal output. If you set the extra_float_digits parameter to 0, the system restores the previous default setting of rounding the output to 6 significant decimal digits for FLOAT4 values and to 15 significant decimal digits for FLOAT8 values. If you set the extra_float_digits parameter to a negative value, the system further reduces the number of digits. For example, if you set the extra_float_digits parameter to -2, the system rounds the output to 4 digits for FLOAT4 values or 13 digits for FLOAT8 values.

If you set the extra_float_digits parameter to a value greater than 0, the system selects the shortest-precise format.

In addition to regular numeric values, the floating-point types support several IEEE 754 special values:

Infinity -Infinity NaN

Infinity indicates infinity values, -Infinity indicates negative infinity values, and NaN indicates not-a-number values. When you write these values as constants in an SQL statement, you must enclose them with single quotation marks ('). Example: UPDATE table SET x = '-Infinity'. When these values are entered, they are recognized as not case-sensitive.

PolarDB for PostgreSQL (Compatible with Oracle) also supports the SQL-standard notations FLOAT and FLOAT(p) for specifying inexact numeric types. p specifies the minimum acceptable precision in binary digits. PolarDB for PostgreSQL (Compatible with Oracle) accepts FLOAT(1) to FLOAT(24) if the REAL type is selected, or FLOAT(25) to FLOAT(53) if the DOUBLE PRECISION type is selected. Values of p outside the allowed value range cause an error. The FLOAT type with no precision specified is considered as the DOUBLE PRECISION type.

Serial types

This section describes a PolarDB for PostgreSQL (Compatible with Oracle)-specific way to create an auto-increment column. Alternatively, you can use the SQL-standard identity column feature to create an auto-increment column, which is described in the "CREATE TABLE" topic.

The SMALLSERIAL, SERIAL, and BIGSERIAL types are not real types. They are only notational convenience symbols for creating unique identifier columns, which are similar to the AUTO_INCREMENT property that is supported by some other databases. In the current implementation, the following statement:

    CREATE TABLE tablename (
        colname SERIAL
    );

is equivalent to the following statements:

    CREATE SEQUENCE tablename_colname_seq AS integer;
    CREATE TABLE tablename (
        colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
    );
    ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Therefore, PolarDB for PostgreSQL (Compatible with Oracle) creates an integer column and specifies that its default values are assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. In most cases, you may want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident. However, this is not automatic. Finally, the sequence is marked as owned by the column. This way, the sequence is dropped if the column or table is dropped.

To insert the next value of the sequence into the SERIAL column, specify that the SERIAL column is assigned its default value. This can be implemented by excluding the column from the list of columns in the INSERT statement, or using the DEFAULT keyword.

The SERIAL and SERIAL4 types are equivalent. Both create INTEGER columns. The BIGSERIAL and SERIAL8 types are equivalent. Both create BIGINT columns. You can use the BIGSERIAL type if you use more than 2³¹ identifiers over the lifetime of the table. The SMALLSERIAL and SERIAL2 types are equivalent. Both create SMALLINT columns.

The sequence created for a SERIAL column is automatically dropped if the column is dropped. You can drop the sequence without dropping the column, but this forcibly deletes the default value expression of the column.