Postgresql 9.3: Add Unique constraint using an existing unique index 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.


I tried:

CREATE UNIQUE INDEX CONCURRENTLY package_tmp_id_idx ON answer (package_id);


Followed by:

prod_db=&gt; 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?
You don't specify the column for the unique constraint. That's not necessary because the column list is defined through the index:

ALTER TABLE package ADD CONSTRAINT 
     unique_package_id UNIQUE USING INDEX package_tmp_id_idx;


There is an example for that in the manual:
http://www.postgresql.org/docs/current/static/sql-altertable.html

You need to scroll down to the end of the page