PostgreSQL: serial vs identity

Question

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?

question from:https://stackoverflow.com/questions/55300370/postgresql-serial-vs-identity

Answer 1

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