Serial vs Identity in PostgreSQL: Key Differences and Usage

In PostgreSQL, serial is a legacy pseudo-type that creates an integer column with an associated sequence for auto-incrementing values, while identity columns are the SQL standard-compliant way to auto-generate unique values using GENERATED ALWAYS or GENERATED BY DEFAULT. Identity columns provide more control and are preferred in modern PostgreSQL versions (10+).

Quick Comparison

This table summarizes the main differences between serial and identity columns in PostgreSQL.

Feature	serial	identity
Introduced in	Legacy, before PostgreSQL 10	PostgreSQL 10 and later
SQL Standard	No, PostgreSQL-specific	Yes, SQL standard compliant
Column type	Integer with implicit sequence	Integer with built-in identity property
Sequence management	Separate sequence object created	Managed internally by PostgreSQL
Control over value generation	Limited, sequence can be altered manually	Options: GENERATED ALWAYS or GENERATED BY DEFAULT
Use case	Simple auto-increment	Preferred for new designs and standards compliance

Key Differences

serial is a convenient shorthand that creates an integer column and a linked sequence object behind the scenes. It is not a true data type but a pseudo-type that expands to an integer plus a sequence. This means the sequence is a separate object you can manipulate independently.

identity columns were introduced in PostgreSQL 10 to follow the SQL standard. They embed the auto-increment behavior directly into the column definition. You can specify GENERATED ALWAYS to force PostgreSQL to always generate a value, or GENERATED BY DEFAULT to allow manual inserts that override the generated value.

Because identity columns are part of the SQL standard, they improve portability and provide clearer control over how values are generated. In contrast, serial is simpler but less flexible and considered legacy in modern PostgreSQL development.

Code Comparison

Here is how you create an auto-incrementing primary key using serial:

sql

CREATE TABLE users_serial (
  id serial PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO users_serial (name) VALUES ('Alice'), ('Bob');

SELECT * FROM users_serial;

Output

 id | name 
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Identity Equivalent

Here is the equivalent table using identity columns with GENERATED ALWAYS:

sql

CREATE TABLE users_identity (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO users_identity (name) VALUES ('Alice'), ('Bob');

SELECT * FROM users_identity;

Output

 id | name 
----+-------
  1 | Alice
  2 | Bob
(2 rows)

When to Use Which

Choose serial if you are working with legacy PostgreSQL versions before 10 or need quick simple auto-increment columns without strict standards compliance.

Choose identity columns for new projects on PostgreSQL 10 or later because they follow the SQL standard, offer better control over value generation, and improve portability across database systems.

In general, prefer identity columns for modern, maintainable database design.

Key Takeaways

identity columns are the modern, SQL-standard way to auto-increment in PostgreSQL 10+.

serial is legacy, creating a separate sequence object and less control.

Use GENERATED ALWAYS or GENERATED BY DEFAULT with identity for flexible value generation.

Prefer identity for new projects and serial only for legacy compatibility.

Both produce similar auto-increment behavior but differ in implementation and standards compliance.