Skip to content
pgdeadbolt

add-unique-or-primary-key

Add UNIQUE and PRIMARY KEY from a concurrent index.

Severity: error · Lock: ACCESS EXCLUSIVE

ADD CONSTRAINT ... UNIQUE and ADD PRIMARY KEY build their index under ACCESS EXCLUSIVE, so the table is unusable for the whole build.

Instead of

Section titled “Instead of”
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

Do

Section titled “Do”
CREATE UNIQUE INDEX CONCURRENTLY users_email_idx ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_idx;

For a primary key, the columns must already be NOT NULL; otherwise Postgres scans the table to check. See set-not-null.

To skip this rule for one statement, put -- pgdeadbolt-ignore add-unique-or-primary-key on the line above it.