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?

Selfseeking

dba.stackexchange.com/questions/158988/… – Endow

Also: https://mcmap.net/q/63542/-what-39-s-the-postgresql-datatype-equivalent-to-mysql-auto-increment - but your query is more of the DBA-side – Endow

wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial 2ndquadrant.com/en/blog/postgresql-10-identity-columns – Candlelight

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't accidentally "forget" the sequence usage. You can still force this, using the OVERRIDING 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.

---

IDENTITY columns also have another advantage: they also minimize the grants you need to give to a role in order to allow inserts.

While a table using a SERIAL column requires the INSERT privilege on the table and the USAGE privilege on the underlying sequence this is not needed for tables using an IDENTITY columns. Granting the INSERT privilege is enough.

---

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

Blakley