Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: serial vs identity

Tags:

postgresql

To have an integer auto-numbering primary key on a table, you can use SERIAL

But I noticed the table information_schema.columns has a number of identity_ fields, and indeed, you could create a column with a GENERATED specifier...

What's the difference? Were they introduced with different PostgreSQL versions? Is one preferred over the other?

like image 493
Stijn Sanders Avatar asked Mar 22 '19 13:03

Stijn Sanders


People also ask

What is identity in PostgreSQL?

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.

What is serial in PostgreSQL?

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. When creating a table, this sequence of integers can be created as follows: CREATE TABLE table_name( id SERIAL );

Does Postgres support identity?

PostgreSQL allows you a table to have more than one identity column. Like the SERIAL , the GENERATED AS IDENTITY constraint also uses the SEQUENCE object internally.


1 Answers

serial is the "old" implementation of auto-generated unique values that has been part of Postgres for ages. However that is not part of the SQL standard.

To be more compliant with the SQL standard, Postgres 10 introduced the syntax using generated as identity.

The underlying implementation is still based on a sequence, the definition now complies with the SQL standard. One thing that this new syntax allows is to prevent an accidental override of the value.

Consider the following tables:

create table t1 (id serial primary key); create table t2 (id integer primary key generated always as identity); 

Now when you run:

insert into t1 (id) values (1); 

The underlying sequence and the values in the table are not in sync any more. If you run another

insert into t1 default_values; 

You will get an error because the sequence was not advanced by the first insert, and now tries to insert the value 1 again.

With the second table however,

insert into t2 (id) values (1); 

Results in:

ERROR: cannot insert into column "id" Detail: Column "id" is an identity column defined as GENERATED ALWAYS. 

So you can accidentally "forget" the sequence usage. You can still force this, using the override system value option:

insert into t2 (id) overriding system value values (1); 

which still leaves you with a sequence that is out-of-sync with the values in the table, but at least you were made aware of that.


It is recommended to use the new identity syntax rather than serial

like image 176
a_horse_with_no_name Avatar answered Sep 21 '22 20:09

a_horse_with_no_name