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 useOVERRIDING SYSTEM VALUE.GENERATED BY DEFAULT— PostgreSQL generates a value unless you supply one explicitly.sequence_options— optional settings such asSTART WITHandINCREMENT BY.- Column type must be
SMALLINT,INT, orBIGINT.
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
| Feature | SERIAL | GENERATED ALWAYS AS IDENTITY |
|---|---|---|
| SQL standard | No | Yes (SQL:2003) |
| Minimum PG version | All | PG 10+ |
| Prevents manual inserts | No | Yes (ALWAYS variant) |
| Custom sequence options | No (post-create only) | Yes (inline) |
| Recommended for new tables | No | Yes |
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 IDENTITYoverSERIALfor all new tables — it is the SQL standard and prevents accidental overwrites. - Use
GENERATED BY DEFAULTonly during initial data migrations where you must control IDs explicitly; switch toALWAYSafter 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 IDENTITYfor tables expected to grow beyond 2 billion rows —INTexhausts at ~2.1 billion.