How to Add a Primary Key in PostgreSQL
Last updated August 26, 2026 · By the SaturnSQL team
Use ALTER TABLE ... ADD PRIMARY KEY (column) on an existing table, or declare GENERATED ALWAYS AS IDENTITY PRIMARY KEY when creating one. The column must be NOT NULL and contain no duplicates.
ALTER TABLE customers ADD PRIMARY KEY (id);Auto-incrementing key on a new table
CREATE TABLE events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);IDENTITY is the modern replacement for SERIAL: it is SQL-standard and keeps the sequence tied to the column. Adding a primary key builds a unique index, which locks the table; on large tables create a unique index CONCURRENTLY first, then attach it with ADD CONSTRAINT ... PRIMARY KEY USING INDEX.
Adding one without a long write lock
ALTER TABLE ADD PRIMARY KEY builds the unique index while holding an ACCESS EXCLUSIVE lock, so writes queue behind it for the whole build. On a large table, build the index concurrently first and then attach it to the constraint, which keeps the exclusive lock down to a moment.
CREATE UNIQUE INDEX CONCURRENTLY orders_pkey ON orders (id);
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;Common errors
ERROR: column "id" of relation "orders" contains null values
A primary key implies NOT NULL. Postgres checks the existing rows before it will add the constraint.
Fill or delete the null rows first, then add the key.
SELECT count(*) FROM orders WHERE id IS NULL;
UPDATE orders SET id = nextval('orders_id_seq') WHERE id IS NULL;
ALTER TABLE orders ADD PRIMARY KEY (id);ERROR: could not create unique index "orders_pkey" DETAIL: Key (id)=(1) is duplicated.
The primary key is backed by a unique index, and the column already holds the same value more than once. The DETAIL line names the first offending value.
Find every duplicate, decide which row survives, then add the key.
SELECT id, count(*) FROM orders GROUP BY id HAVING count(*) > 1;ERROR: multiple primary keys for table "orders" are not allowed
The table already has a primary key. A table gets exactly one, however many unique constraints you add.
Look up the existing key, and drop it first if you really mean to replace it.
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'p';
ALTER TABLE orders DROP CONSTRAINT orders_pkey;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free