Skip to content
All glossary terms

Identity column

What is an identity column in PostgreSQL?

An identity column gets its values automatically from an implicit sequence. It is the SQL-standard way to create an auto-incrementing key in PostgreSQL.

An identity column is declared with GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY. PostgreSQL attaches an implicit sequence to it and fills the column from that sequence whenever an insert does not provide a value.

With GENERATED ALWAYS, an explicit value in an INSERT is rejected unless the statement says OVERRIDING SYSTEM VALUE; with BY DEFAULT, an explicit value takes precedence. Identity columns follow the SQL standard, while serial is an older PostgreSQL-specific shorthand. As with any sequence, numbers used by a rolled-back transaction are not reused, so gaps are normal.

Example

The example inserts two rows without ids, returns the generated values and shows the name of the sequence behind the column.

CREATE TABLE tickets (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  subject text NOT NULL
);
INSERT INTO tickets (subject) VALUES ('first'), ('second') RETURNING id;

SELECT pg_get_serial_sequence('tickets', 'id');

Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.

Sources

Official PostgreSQL documentation (in English).

Related terms