Menu

PostgreSQL Primary Keys: Identity, SERIAL and UUID

How to define a primary key in PostgreSQL, and how to generate it: identity columns, the older SERIAL, and UUIDs with gen_random_uuid(). Also composite keys and resetting a sequence.

This page includes runnable editors. Edit, run, and see output instantly.

A primary key is the column, or set of columns, that identifies each row. Its values must be unique and not NULL, and a table can have only one. In PostgreSQL you usually let the database generate it with an identity column: id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

The ids are 1 and 2. Defining the key also creates a unique index on it, named users_pkey.

Identity columns: ALWAYS vs BY DEFAULT

Identity columns are the SQL standard way to number rows, available since PostgreSQL 10. There are two forms:

  • GENERATED ALWAYS AS IDENTITY rejects an explicit value in INSERT, unless the insert says OVERRIDING SYSTEM VALUE. This is the safer default.
  • GENERATED BY DEFAULT AS IDENTITY uses the generated value only when you do not supply one.

With BY DEFAULT, inserting your own ids leaves the sequence behind, and the next generated id collides. After importing rows with explicit ids, move the sequence past the highest one with setval:

Without the setval line, the last insert would get id 1 and fail with a duplicate key error. With it, the new row gets 4.

SERIAL vs identity

serial (4 bytes) and bigserial (8 bytes) are not real types. They are shorthand that creates a sequence and sets the column's default to nextval() of it:

The column is a plain integer with a nextval('legacy_id_seq'::regclass) default. Nothing stops an insert from writing its own id, and the sequence needs its own permissions. Identity columns fix both, so prefer them in new tables. serial still works, and you will meet it in older schemas and tutorials.

Use bigint (or bigserial) for tables that can grow. An integer key runs out after about 2.1 billion values.

UUID primary keys

gen_random_uuid() returns a random version 4 UUID. It is built in since PostgreSQL 13, so no extension is needed:

Each run prints different ids, 36 characters each in text form. The uuid type stores them in 16 bytes, so use it rather than text. PostgreSQL 18 adds uuidv7(), which generates time-ordered UUIDs that insert in index order. On PostgreSQL 17, generate those in your application.

Composite primary keys

A key can span several columns, written on its own line in CREATE TABLE. It is the natural key for a link table, where each pair may appear once:

The repeated pair (1, 10) is skipped, so the count is 3. To add a key to an existing table, use ALTER TABLE t ADD PRIMARY KEY (col). The ALTER TABLE page covers the other changes. Other tables point at the key with foreign keys.

Frequently Asked Questions

Does PostgreSQL have AUTO_INCREMENT?

Not by that name. The standard way is an identity column, id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, available since PostgreSQL 10. The older serial and bigserial types do the same with a sequence and a default.

Why does my PostgreSQL id sequence have gaps?

A sequence hands out a value even when the insert later fails or its transaction rolls back, and that value is never reused. Gaps are normal and harmless, so do not rely on ids being consecutive.

Does a primary key create an index in PostgreSQL?

Yes. A primary key (and every UNIQUE constraint) creates a unique B-tree index automatically, named tablename_pkey for the primary key.

Should I use UUID or bigint for a primary key?

bigint identity keys are smaller (8 bytes against 16) and insert in index order. UUIDs can be generated anywhere without a round trip and do not reveal how many rows exist. Random UUIDs scatter inserts across the index, which costs more on large tables.

Coddy programming languages illustration

Learn to code with Coddy

Get started