Better to use SERIAL PRIMARY KEY or GENERATED ALWAYS AS IDENTITY for primary key in PostgreSQL

Question

Not sure which option is latest best practice? I read on on this tutorial that:

https://www.postgresqltutorial.com/postgresql-identity-column/

PostgreSQL version 10 introduced a new constraint GENERATED AS IDENTITY that allows you to automatically assign a unique number to a column.

The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.

In the example they use the identity as the primary key:

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

When you reference this table for a FOREIGN KEY as per the below:

CREATE TABLE pallet (
    id INT GENERATED ALWAYS AS IDENTITY,
    color_1 REFERENCES color
    color_2 REFERENCES color
);

Will it know that the identity is the primary key now?:

score 18 · Accepted Answer · edited Sep 23 '20 at 06:25

Will it know that the identity is the primary key now?

No (and neither would a serial do that).

You need to define the primary key explicitly:

CREATE TABLE color (
    color_id INT  primary key GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

Not sure which option is latest best practice?

It's recommended to use identity instead of serial.

Quote from the Postgres Wiki

For new applications, identity columns should be used instead.

Why not serial?
The serial types have some weird behaviors that make schema, dependency, and permission management unnecessarily cumbersome.

Finally, identity columns conform with the SQL standard, while serial is PostgreSQL dialect.

Better to use SERIAL PRIMARY KEY or GENERATED ALWAYS AS IDENTITY for primary key in PostgreSQL

1 Answers1