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

❯ cat unique.sql            
ALTER TABLE IF EXISTS foo ADD COLUMN IF NOT EXISTS bar text CONSTRAINT foo_bar_unique UNIQUE;
❯ squawk unique.sql 
Found 0 issues in 1 file 🎉
❯ squawk --version  
squawk 0.26.0

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
• Adding column with unique constraint is blocking premium-minds/sonar-postgres-plugin#30

[chdsbd]

I think we could update the existing disallowed-unique-constraint rule to cover this