Identity Column in PostgreSQL

Learn how to use PostgreSQL GENERATED AS IDENTITY to create SQL-standard auto-increment identity columns, including ALWAYS vs BY DEFAULT and custom sequence options.

5 min read · PostgreSQL 10+ · Back to overview

Quick Answer

PostgreSQL identity columns use GENERATED AS IDENTITY (introduced in version 10) for SQL-standard auto-incrementing columns. GENERATED ALWAYS prevents manual inserts; GENERATED BY DEFAULT allows them. Both are backed by an internal sequence and are preferred over the legacy SERIAL pseudo-type.

Spin up a Postgres database in 20 seconds with Vela.

Try Vela Sandbox

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

Syntax

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
  [(sequence_options)]
  • GENERATED ALWAYS — PostgreSQL always generates the value; user-supplied values raise an error unless you use OVERRIDING SYSTEM VALUE.
  • GENERATED BY DEFAULT — PostgreSQL generates a value unless you supply one explicitly.
  • sequence_options — optional settings such as START WITH and INCREMENT BY.
  • Column type must be SMALLINT, INT, or BIGINT.

Practical Example

Create a products table with a GENERATED ALWAYS identity primary key:

CREATE TABLE products (
  product_id  INT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         VARCHAR(50)  NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL,
  unit_price  NUMERIC(10,2) NOT NULL DEFAULT 0
);

INSERT INTO products (sku, name, unit_price)
VALUES ('WID-001', 'Basic Widget', 9.99);
-- product_id is automatically 1

-- Attempting to specify product_id raises an error:
INSERT INTO products (product_id, sku, name, unit_price)
VALUES (5, 'WID-002', 'Premium Widget', 24.99);
-- ERROR: cannot insert into column "product_id"

-- Use OVERRIDING SYSTEM VALUE when you must specify the ID (e.g., data migrations):
INSERT INTO products (product_id, sku, name, unit_price)
OVERRIDING SYSTEM VALUE
VALUES (5, 'WID-002', 'Premium Widget', 24.99);

Use GENERATED BY DEFAULT when you need to control IDs during a migration:

CREATE TABLE product_archive (
  product_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name       VARCHAR(200) NOT NULL
);

-- Explicit ID allowed:
INSERT INTO product_archive (product_id, name) VALUES (100, 'Legacy Item');

-- System-generated also works:
INSERT INTO product_archive (name) VALUES ('New Item');

Custom sequence options — start at 1000, increment by 10:

CREATE TABLE order_lines (
  line_id    INT GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10),
  order_id   INT NOT NULL,
  item_name  VARCHAR(200) NOT NULL
);

Add an identity constraint to an existing column:

ALTER TABLE products
  ALTER COLUMN product_id
  ADD GENERATED ALWAYS AS IDENTITY;

Remove an identity constraint without dropping the column:

ALTER TABLE products
  ALTER COLUMN product_id
  DROP IDENTITY IF EXISTS;

SERIAL vs GENERATED AS IDENTITY

FeatureSERIALGENERATED ALWAYS AS IDENTITY
SQL standardNoYes (SQL:2003)
Minimum PG versionAllPG 10+
Prevents manual insertsNoYes (ALWAYS variant)
Custom sequence optionsNo (post-create only)Yes (inline)
Recommended for new tablesNoYes

Testing with Vela

When migrating a schema from SERIAL to GENERATED AS IDENTITY, Vela database branching lets you apply and test the ALTER TABLE migration against a copy of production data. Verify that the existing sequence state is preserved, that application inserts work correctly with the new identity constraint, and that no duplicate-key errors occur before promoting the change to production.

Production Tips

  • Prefer GENERATED ALWAYS AS IDENTITY over SERIAL for all new tables — it is the SQL standard and prevents accidental overwrites.
  • Use GENERATED BY DEFAULT only during initial data migrations where you must control IDs explicitly; switch to ALWAYS after the load.
  • Identity column values are not transaction-safe: rolled-back transactions leave gaps in the sequence. This is normal and expected.
  • A table can have more than one identity column in PostgreSQL.
  • Use BIGINT GENERATED ALWAYS AS IDENTITY for tables expected to grow beyond 2 billion rows — INT exhausts at ~2.1 billion.

Continue in Managing Tables: Generated Columns.

Related in this section: PostgreSQL Data Types · Create Table · Select Into

Frequently Asked Questions

What does GENERATED AS IDENTITY do in PostgreSQL?
GENERATED AS IDENTITY declares that a column's value is automatically produced by an internal sequence. GENERATED ALWAYS raises an error if you attempt to insert an explicit value without OVERRIDING SYSTEM VALUE. GENERATED BY DEFAULT uses the sequence value unless you supply one explicitly.
Does an identity column lock the table and block queries during inserts?
No. Identity columns use the same lock-free sequence mechanism as SERIAL. Concurrent inserts from multiple sessions each receive a unique value without blocking one another. The table lock during an INSERT is brief and unrelated to sequence generation.
What happens to dependent objects when an identity column is altered or dropped?
If you drop the identity constraint with ALTER COLUMN ... DROP IDENTITY, the column remains but loses its automatic value generation and the internal sequence is dropped. If you drop the entire column, the sequence is dropped with it.
Can I use IF EXISTS when dropping an identity constraint?
Yes. ALTER TABLE t ALTER COLUMN col DROP IDENTITY IF EXISTS suppresses the error when no identity constraint exists on the column, making the statement safe to run in idempotent migration scripts.
What is the safest way to use identity columns in production?
Use GENERATED ALWAYS AS IDENTITY for new tables to prevent accidental manual inserts. Use GENERATED BY DEFAULT only when you need to explicitly control IDs during initial data migrations. For very large tables, declare the column as BIGINT to avoid exhausting the integer range.