PolarDB:Numeric types

Integer types

INTEGER stores whole numbers from -2,147,483,648 to 2,147,483,647. Values outside this range cause an error.

Use SMALLINT only when disk space is at a premium. Use BIGINT only when values exceed the INTEGER range.

ROWID columns hold the physical address of a row as fixed-length binary data. Despite the table entry listing ROWID as a signed 8-byte integer, ROWID actually stores unsigned 4-byte integers in the range 0 to 4,294,967,295. Values outside this range cause an error.

Arbitrary-precision numeric types

NUMBER stores numbers with virtually unlimited precision and performs calculations exactly. Use NUMBER for monetary amounts and other values where precision loss is unacceptable. Note that NUMBER is significantly slower than the floating-point types described in the next section.

Precision and scale — The precision of a NUMBER is the total count of significant digits on both sides of the decimal point. The scale is the count 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 zero.

Declare a NUMBER column using one of the following forms:

NUMBER(precision, scale)
NUMBER(precision)          -- scale defaults to 0
NUMBER                     -- accepts any precision and scale up to the implementation limit

The precision must be positive; the scale must be zero or positive.

A NUMBER column with a declared scale coerces input values to that scale. A NUMBER column without precision or scale does not coerce input. For maximum portability, specify both precision and scale explicitly.

If an input value exceeds the declared precision or scale, PolarDB rounds it. If the value cannot be rounded to fit, an error is raised.

Example: A column declared as NUMBER(3, 1) rounds values to one decimal place and accepts values between -99.9 and 99.9 inclusive.

NUMERIC and DECIMAL are equivalent to NUMBER.

Floating-point types

REAL and DOUBLE PRECISION are inexact, variable-precision types. They implement IEEE Standard 754 for binary floating-point arithmetic (single and double precision, respectively), subject to the capabilities of the underlying processor, operating system, and compiler.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so storing and retrieving a value may show slight discrepancies. Keep the following in mind when using floating-point types:

• For exact storage and calculations — such as for monetary amounts — use NUMBER instead.

• For complex calculations where boundary behavior (infinity, underflow) matters, evaluate the implementation carefully before relying on it.

• Comparing two floating-point values for equality may not work as expected.

On most platforms, REAL has a range of at least 1E-37 to 1E+37 with at least 6 decimal digits of precision. DOUBLE PRECISION typically has a range of around 1E-307 to 1E+308 with at least 15 digits of precision. Values too large or too small cause an error. Input with too many significant digits is rounded. Values too close to zero to be represented as distinct from zero cause an underflow error.

PolarDB also supports the SQL standard notations FLOAT and FLOAT(p), where p specifies the minimum acceptable precision in binary digits:

Values of p outside the supported range cause an error.

Serial types

SMALLSERIAL, SERIAL, and BIGSERIAL are not true numeric types. They are a convenience shorthand for creating unique identifier columns, similar to AUTO_INCREMENT in other databases.

The following declaration:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to:

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

This creates an integer column with a NOT NULL constraint and a default value drawn from a sequence generator. Add a UNIQUE or PRIMARY KEY constraint separately if you need to prevent duplicate values.

When inserting a row, assign the next sequence value to the SERIAL column by either omitting it from the column list or using the DEFAULT keyword.

Type equivalents:

Use BIGSERIAL if the table may generate more than 2^31 identifiers over its lifetime.

When a SERIAL column is dropped, its underlying sequence is also dropped automatically. Dropping only the sequence leaves the column intact but removes its default value expression.