TopolearnEvery table needs a key generator, and the modern options avoid serial's permission and ownership quirks.
The modern way to auto-generate integer keys is an identity column, standard SQL since PostgreSQL 10:
GENERATED ALWAYS AS IDENTITYrejects explicit values unless you writeOVERRIDING SYSTEM VALUE— good protection against accidental manual ids.GENERATED BY DEFAULT AS IDENTITYuses the sequence unless you supply a value — convenient for imports.
You'll still see the older serial / bigserial pseudo-types. They create a separate sequence plus a default, which causes quirks with permissions and copying table definitions; prefer identity in new schemas.
Both are backed by a sequence, which hands out numbers without waiting for transactions to finish. Consequence: a rolled-back insert still consumes its number, so ids have gaps. Never rely on ids being contiguous or use them as a count.
For UUIDs, use the uuid type (16 bytes, not text). gen_random_uuid() (built in since 13) gives random version-4 UUIDs. PostgreSQL 18 adds uuidv7(), which is time-ordered: new values sort after older ones, so B-tree inserts stay at the right edge of the index instead of landing at random pages. Prefer v7 for primary keys when you choose UUIDs.
After a bulk load with explicit ids, move the sequence past the maximum with setval, or the next insert will collide.
Example
CREATE TABLE customer (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE api_key (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- uuidv7() on 18+
owner bigint NOT NULL REFERENCES customer(id)
);
INSERT INTO customer (name) VALUES ('Ani'), ('Davit');
INSERT INTO customer (id, name) VALUES (99, 'Manual');
-- ERROR: cannot insert a non-DEFAULT value into column "id"
BEGIN; INSERT INTO customer (name) VALUES ('Rolled back'); ROLLBACK;
INSERT INTO customer (name) VALUES ('Lilit');
SELECT id, name FROM customer ORDER BY id; -- 1 Ani, 2 Davit, 4 Lilit (gap)
INSERT INTO api_key (owner) VALUES (1) RETURNING id;
-- e.g. 0b6f3c1e-8a7d-4c2e-9f51-3d2a6b7c8e90Remember
- Prefer GENERATED ... AS IDENTITY over serial
- Sequences never roll back, so ids have gaps
- Store UUIDs in the uuid type; uuidv7() keeps inserts index-friendly
Watch out for
- Storing UUIDs as text, which doubles their size and slows comparisons.