What is the limit of GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY in PostgreSQL?

Question

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)?

Answer 1

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');

db<>fiddle demo

Output:

Answer 2

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, serial and bigserial are 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 bigint arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807).

IDENTITY columns do the same, only the SEQUENCE is exclusively bound to the owning column, which avoids some oddities that serial "types" exhibit.

See:

• Auto increment table column