In PostgreSQL, why is GENERATED AS IDENTITY preferred over serial or bigserial for a new table's primary key?
answer
- serial is notation, not a type
- a default that calls nextval()
- who needs USAGE on the sequence
- LIKE copies the old default
- integer stops at 2,147,483,647
basics
~20 sserial is shorthand for a separate sequence plus a nextval() default; an identity column owns an internal sequence. Identity needs no sequence grant, copies cleanly with LIKE, can reject hand-written ids, and is the SQL-standard form.
solid answer
~50 s`serial` is not a real type. PostgreSQL expands `order_id serial` into an `integer` column, a sequence created `AS integer`, a `DEFAULT nextval('orders_order_id_seq')`, `NOT NULL`, and `OWNED BY` so the sequence is dropped with the column. Because the plumbing is an ordinary default, it leaks: an application role needs `USAGE` on the sequence as well as `INSERT` on the table, `CREATE TABLE ... (LIKE orders INCLUDING DEFAULTS)` copies a default that still draws from the old sequence, and any client may write its own id. `order_id bigint GENERATED ALWAYS AS IDENTITY` makes the sequence part of the column: inserting needs no sequence privilege, `INCLUDING IDENTITY` creates a fresh sequence, an explicit id fails with SQLSTATE `428C9` unless the statement says `OVERRIDING SYSTEM VALUE`, and its options change through `ALTER TABLE ... ALTER COLUMN`. Declare it `bigint`: `serial` stops at 2,147,483,647.
code
sql · 13 linesCREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
shard_id smallint NOT NULL,
placed_at timestamptz NOT NULL DEFAULT now()
);
-- the application role needs no grant on the implicit sequence
GRANT INSERT, SELECT ON orders TO checkout_app;
INSERT INTO orders (shard_id) VALUES (3) RETURNING order_id;
-- a deliberate explicit id must say so
INSERT INTO orders (order_id, shard_id) OVERRIDING SYSTEM VALUE VALUES (900001, 3);go deeper
Recall that serial expands to a sequence plus a nextval() default, and that new tables should use bigint GENERATED ALWAYS AS IDENTITY with a primary key.
Explain the concrete differences: sequence privileges, LIKE copying the old default, the 428C9 rejection of explicit ids, and changing options through ALTER TABLE rather than ALTER SEQUENCE.
Show you can migrate a live serial column to identity in place, resync its sequence, and spot an integer key heading for 2,147,483,647 before it fails.
Frame the key-type decision for the schema's lifetime: bigint identity now versus a later column rewrite, and where uuid keys or per-shard ranges fit the service's growth.
## What serial really is In PostgreSQL, `smallserial`, `serial` and `bigserial` are **not true data types**. They are a notational shortcut the server expands at `CREATE TABLE` time. Writing `order_id serial` for the checkout service's `orders` table is equivalent to: ```sql CREATE SEQUENCE orders_order_id_seq AS integer; CREATE TABLE orders ( order_id integer NOT NULL DEFAULT nextval('orders_order_id_seq') ); ALTER SEQUENCE orders_order_id_seq OWNED BY orders.order_id; ``` Three separate objects cooperate: an `integer` column, an independent **sequence** (a one-row generator object), and a **column default** that calls `nextval()`. `OWNED BY` only ties their lifetimes together, so dropping the column drops the sequence. No `UNIQUE` or `PRIMARY KEY` is added; you still declare that yourself. ## What an identity column changes `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` is the SQL-standard way to ask for the same result, and PostgreSQL implements it with an **implicit sequence that belongs to the column** rather than with a default expression. An ordinary `INSERT` behaves the same; the edges differ: | Concern | `serial` / `bigserial` | `GENERATED ... AS IDENTITY` | |---|---|---| | What it is | notation expanding to sequence + `nextval()` default + `NOT NULL` | column property with an implicit sequence; `NOT NULL` implied | | Privileges to insert | `INSERT` on the table **and** `USAGE` (or `UPDATE`) on the sequence | `INSERT` on the table only | | A client supplies its own id | silently accepted | `ALWAYS`: rejected unless `OVERRIDING SYSTEM VALUE`; `BY DEFAULT`: accepted | | `CREATE TABLE ... (LIKE ...)` | `INCLUDING DEFAULTS` copies a default still calling the **original** sequence | `INCLUDING IDENTITY` creates a **new** sequence for the copy | | Changing cache, increment, restart | `ALTER SEQUENCE` on a name you must know | `ALTER TABLE ... ALTER COLUMN ... SET CACHE` / `RESTART` | | Standard | PostgreSQL-only spelling | SQL standard | The privilege row bites in production: with `serial`, a role granted only `INSERT` on `orders` fails on its first insert with a permission error on the sequence, because the default calls `nextval()`, which checks `USAGE` or `UPDATE`. An identity column's generator is invoked internally without that check. ## The messages to recognise - An explicit value into a `GENERATED ALWAYS` identity: `cannot insert a non-DEFAULT value into column`, SQLSTATE `428C9` (`generated_always`), with the hint `Use OVERRIDING SYSTEM VALUE to override.` - `UPDATE orders SET order_id = 5` on the same column: `can only be updated to DEFAULT`; `UPDATE` admits no `OVERRIDING` clause. - A sequence that has run out: `nextval: reached maximum value of sequence`, SQLSTATE `2200H`. ## Pick bigint up front `serial` creates an `integer` column and a sequence `AS integer`, so it stops at **2,147,483,647**. Sequences default to `NO CYCLE`, so the next insert errors instead of wrapping. An identity column takes the column's type, so `integer GENERATED ... AS IDENTITY` has the same ceiling. Declaring `bigint` costs four bytes per row and per index entry; widening later with `ALTER COLUMN ... TYPE bigint` rewrites the table and its indexes, which is far more expensive on a hot table. ## Partitioned tables and inheritance Since PostgreSQL 17 a partitioned table can carry an identity column; every partition inherits it and **cannot have its own**, so all partitions draw from one sequence. Under old-style table inheritance a child does not inherit identity properties: the identity that applies is the one on the table named in the statement. ## Converting an existing serial column Because the old pieces are separate objects, the migration is a few catalog statements: 1. `ALTER TABLE orders ALTER COLUMN order_id DROP DEFAULT;` because `ADD GENERATED` refuses a column that still has a default. 2. `DROP SEQUENCE orders_order_id_seq;` 3. `ALTER TABLE orders ALTER COLUMN order_id ADD GENERATED BY DEFAULT AS IDENTITY;` The column must already be `NOT NULL`, which `serial` guaranteed, and existing rows are not changed. 4. Move the new sequence past the data with `SELECT setval(pg_get_serial_sequence('orders', 'order_id'), max(order_id)) FROM orders;` Use `BY DEFAULT` while legacy code still writes ids, then `SET GENERATED ALWAYS` once it stops. ## What identity does not promise An identity column is `NOT NULL` but **not unique** on its own: a `RESTART`, a `setval()`, or a value written with `OVERRIDING SYSTEM VALUE` can repeat a number, so the `PRIMARY KEY` is what enforces uniqueness. Like any sequence it also leaves gaps; it is a uniqueness tool, not a counter.
- How do you convert an existing serial column to an identity column in place?Drop the column default, drop the old sequence, then run `ALTER TABLE orders ALTER COLUMN order_id ADD GENERATED BY DEFAULT AS IDENTITY`; the column must be `NOT NULL` and existing rows are not changed. Finish with `SELECT setval(pg_get_serial_sequence('orders', 'order_id'), max(order_id)) FROM orders` so the new sequence starts above the data, and switch to `SET GENERATED ALWAYS` once no code writes ids.
- Does an identity column on its own stop two orders from getting the same order_id?No. PostgreSQL marks it `NOT NULL` but documents that identity does not guarantee uniqueness: `ALTER TABLE ... ALTER COLUMN ... RESTART`, `setval()`, a `BY DEFAULT` column accepting a client's value, or `OVERRIDING SYSTEM VALUE` can each reintroduce an existing number. Uniqueness comes from the `PRIMARY KEY` or a `UNIQUE` constraint, which is why the declaration carries both.
- serial is a real integer type that keeps its own counter inside the column
- Granting INSERT on the table is all a role needs for a serial column
- GENERATED ALWAYS AS IDENTITY makes the column unique without a primary key
- A serial column's sequence is bigint, so it can never run out
- LIKE ... INCLUDING DEFAULTS gives the copied table its own fresh sequence