Menu
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)?
708
The GENERATED BY DEFAULT generates sequential integers for the identity column. However, if you provide a value for insert or update, the database system will use that value for insert instead of using the auto-generated value.
In PostgreSQL, the GENERATED AS IDENTITY constraint is used to create a PostgreSQL identity column. It allows users to automatically assign a unique value to a column. The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the PostgreSQL's SERIAL column.
By simply setting our id column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our id column with a unique, primary key value for every INSERT .
PostgreSQL has a special kind of database object generator called SERIAL. It is used to generate a sequence of integers which are often used as the Primary key of a table. Syntax: variable_name SERIAL. When creating a table, this sequence of integers can be created as follows: CREATE TABLE table_name( id SERIAL );
The type can be SMALLINT, INT, or BIGINT. The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) a value into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error.
The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the PostgreSQL’s SERIAL column. Let’s analyze the above syntax. The type can be SMALLINT, INT, or BIGINT. The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column.
The Postgres documentation CREATE TABLE says for GENERATED BY DEFAULT AS IDENTITY (sequence_options) that: The optional sequence_options clause can be used to override the options of the sequence. See CREATE SEQUENCE for details. The documentation CREATE SEQUENCE no longer calls them as options but parameters.
However, if you provide a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value. Now let’s look into some examples.
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:
133
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:
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:
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
