pgsafe_

← all rules

add-primary-key-without-index

error

ADD PRIMARY KEY without a prebuilt index

Why it's unsafe

Adding a PRIMARY KEY inline builds its unique index (and may scan for NOT NULL) under an ACCESS EXCLUSIVE lock.

Safe rewrite

Build the index with CREATE UNIQUE INDEX CONCURRENTLY, then attach it: ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX idx.

Example

Try it in the playground →

Unsafe

ALTER TABLE users ADD PRIMARY KEY (id);

Safe

CREATE UNIQUE INDEX CONCURRENTLY users_pkey ON users (id);
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_pkey;

Related