Postgresql 9.3: Add Unique constraint using an existing unique index

Question

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=> 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?

• András Váczi: I tend not to add a unique constraint at all. This way, it is somewhat easier to solve issues with bloated indexes on heavily used tables.

Answer 1

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

• Adam Matan: Thanks. This query works syntactically, but it does not return for a long time (more than 5 minutes on a table with 3,000 rows). Could it be waiting for other transactions to finish?
• user1822: @AdamMatan: yes the ALTER TABLE needs an exclusive lock (although for only a short time). If you have uncommitted transactions, then those will block this. Check for sessions that have the state "idle in transaction" in pg_stat_activity
• dwanderson: What is the purpose of adding the CONSTRAINT if the UNIQUE INDEX already exists? I tried testing around a bit and it seems that creating the UNIQUE INDEX was sufficient to ensure uniqueness - trying to add duplicate data resulted in duplicate key value violates unique constraint 'package_tmp_id_idx'. I assume I'm missing something though, otherwise it wouldn't be in the manual, so any information is appreciated!
• Aakash Verma: What if my index has an expression? Then I need to specify the columns for the constraint right?
• Sampson Crowley: @dwanderson there are quite a few things that can only be done with constraints but not with index names; like upsert without listing every column in the unique index