I would like to add a unique constraint to a DB field in one of my production databases. Unfortunately, the following statement is blocking:
ALTER TABLE package ADD CONSTRAINT unique_package_id UNIQUE (package_id);
Reading the relevant part from the PostgreSQL manual:
Note: Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time. To do that, create the index using CREATE INDEX CONCURRENTLY, and then install it as an official constraint using this syntax. See the example below.
CREATE UNIQUE INDEX CONCURRENTLY package_tmp_id_idx ON answer (package_id);
prod_db=> ALTER TABLE package ADD CONSTRAINT unique_package_id UNIQUE (package) USING INDEX package_tmp_id_idx;
Which failed with:
ERROR: syntax error at or near "package_tmp_id_idx" LINE 1: ...T unique_package_id UNIQUE (answer_id) USING INDEX package_tmp_id_idx... ^
I've tried playing with the syntax, but could not get it to work.
What's the right syntax to create a UNIQUE constraint using an existing UNIQUE index?