Adding column with unique constraint is blocking

[froque]

ALTER TABLE IF EXISTS foo ADD COLUMN IF NOT EXISTS bar text CONSTRAINT foo_bar_unique UNIQUE;

also blocks updates while the index is built

Solution:

split into 3 steps:

step 1 - create column without constraint

ALTER TABLE IF EXISTS foo ADD COLUMN IF NOT EXISTS bar text;

step 2 - add unique index concurrently

 -- allows reads and writes while index is built
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS foo_bar_idx ON foo (bar);

step 3 - create table constraint from unique index

ALTER TABLE foo ADD CONSTRAINT foo_bar_unique UNIQUE USING INDEX foo_bar_idx;
-- NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "foo_bar_idx" to "foo_bar_unique"

• https://www.postgresql.org/docs/16/sql-altertable.html