skip to content

UUIDs, Serial & Generated Columns

PostgreSQL generates keys with serial or IDENTITY sequences and uuid functions, and derives values with generated columns, virtual by default in 18. Interviewers probe serial vs IDENTITY and uuidv7.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, why is GENERATED AS IDENTITY preferred over serial or bigserial for a new table's primary key?

level: juniorimportance: must knowfreq 72%

answer

  1. serial is notation, not a type
  2. a default that calls nextval()
  3. who needs USAGE on the sequence
  4. LIKE copies the old default
  5. integer stops at 2,147,483,647

basics

~20 s

serial 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 lines
sql
CREATE 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

for a junior

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.

for a middle

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.

for a senior

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.

for a principal

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
open as a page

In PostgreSQL 18, how does uuidv7() differ from gen_random_uuid(), and what ordering does uuidv7() actually guarantee?

level: middleimportance: must knowfreq 55%

basics

~20 s

gen_random_uuid(), also spelled uuidv4() in 18, returns a fully random version 4 UUID. uuidv7() puts a millisecond Unix timestamp first, so values sort by creation time: strictly increasing within one session, only roughly time-ordered across sessions.

open as a page

After COPY loads legacy order ids into a PostgreSQL identity column, why does the next INSERT fail, and how does setval fix it?

level: middleimportance: should knowfreq 40%

basics

~20 s

COPY FROM writes the supplied ids even into a GENERATED ALWAYS column without calling nextval(), so the sequence still offers numbers the loaded rows hold. Resync it with setval(pg_get_serial_sequence('orders', 'order_id'), max(order_id)) while writers are paused.

open as a page

In PostgreSQL 18, why does CREATE INDEX fail on full_name GENERATED ALWAYS AS (first_name || ' ' || last_name), and what fixes it?

level: middleimportance: should knowfreq 45%

basics

~10 s

In PostgreSQL 18 a generated column without STORED is VIRTUAL: computed on read and never stored, so it cannot be indexed. Declare it STORED, or keep it virtual and index the same expression directly.

open as a page

A PostgreSQL checkout service set CACHE 100 on its order_id identity; why do ids now arrive out of order and jump by hundreds?

level: seniorimportance: should knowfreq 30%

basics

~20 s

PostgreSQL's sequence CACHE is per session: each backend reserves 100 values and serves them from private memory. Sessions hand out interleaved blocks, and unused values vanish when a session ends, so ids stay distinct but are neither ordered nor dense.

open as a page