Identity Column

PostgreSQL 10 introduced the GENERATED AS IDENTITY constraint as the SQL-standard replacement for the legacy SERIAL pseudo-type. Identity columns are backed by an internal sequence like SERIAL, but they give you more explicit control over whether user-supplied values are permitted, and they are portable to other SQL databases that implement the standard.

GENERATED AS IDENTITY syntax

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
  [(sequence_option)]

• GENERATED ALWAYS — PostgreSQL always generates the value. Attempting to insert a user-supplied value raises an error unless you use OVERRIDING SYSTEM VALUE.
• GENERATED BY DEFAULT — PostgreSQL generates a value unless you supply one explicitly. This behaves more like SERIAL.
• sequence_option — optional sequence settings such as START WITH and INCREMENT BY.
• The column type must be SMALLINT, INT, or BIGINT.

Identity column examples

GENERATED ALWAYS example:

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

INSERT INTO color (color_name) VALUES ('Red');
-- Works fine, color_id = 1

INSERT INTO color (color_id, color_name) VALUES (2, 'Green');
-- ERROR: cannot insert into column "color_id"
-- HINT: Use OVERRIDING SYSTEM VALUE to override.

-- Override when needed:
INSERT INTO color (color_id, color_name)
OVERRIDING SYSTEM VALUE
VALUES (2, 'Green');

GENERATED BY DEFAULT example:

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

INSERT INTO color (color_name) VALUES ('White');     -- color_id = 1
INSERT INTO color (color_id, color_name) VALUES (2, 'Yellow'); -- allowed

Custom sequence options:

CREATE TABLE color (
  color_id   INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10),
  color_name VARCHAR NOT NULL
);

INSERT INTO color (color_name) VALUES ('Orange');  -- color_id = 10
INSERT INTO color (color_name) VALUES ('Purple');  -- color_id = 20

Add an identity column to an existing table:

ALTER TABLE shape
  ALTER COLUMN shape_id
  ADD GENERATED ALWAYS AS IDENTITY;

Remove an identity constraint:

ALTER TABLE shape
  ALTER COLUMN shape_id
  DROP IDENTITY IF EXISTS;

Identity column tips

• Prefer GENERATED ALWAYS AS IDENTITY over SERIAL for new tables — it is SQL standard, more explicit, and prevents accidental overwrites of system-generated values.
• Use GENERATED BY DEFAULT when you need to migrate data from another system and must control the IDs explicitly during the initial load.
• Like sequences, identity column values are not transaction-safe — rolled-back transactions leave gaps in the sequence. This is normal.
• A table can have more than one identity column, unlike some other databases.

Reference: PostgreSQL documentation — CREATE TABLE (identity columns).