There are smallserial, serial and bigserial numeric data types in PostgreSQL, which have obvious limits to 32767, 2147483647 and 9223372036854775807 respectively.
But what about GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY, does it have any restrictions? Or maybe they are computed according to the provided data type (SMALLINT, INT, BIGINT)?
CodePudding user response：
Yes, it is dependent on column's data type and could be validated using COLUMNS metadata:
CREATE TABLE t1(id SMALLINT GENERATED ALWAYS AS IDENTITY);
CREATE TABLE t2(id INT GENERATED ALWAYS AS IDENTITY);
CREATE TABLE t3(id BIGINT GENERATED ALWAYS AS IDENTITY);
SELECT table_name, column_name, data_type,
is_identity, identity_minimum, identity_maximum, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN('t1','t2','t3');
CodePudding user response：
There are smallserial, serial and bigserial numeric data types in PostgreSQL, ...
Those are not actual data types to begin with. The manual:
The data types
smallserial,serialandbigserialare not true types, but merely a notational convenience for creating unique identifier columns
The actual data type used is smallint, int and bigint, respectively.
See:
- How to convert primary key from integer to serial?
- Safely rename tables using serial primary key columns
All serial types draw numbers from an owned SEQUENCE, which is based on bigint. The manual:
Sequences are based on
bigintarithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808to9223372036854775807).
IDENTITY columns do the same, only the SEQUENCE is exclusively bound to the owning column, which avoids some oddities that serial "types" exhibit.
See: