6

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?

  • 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. – dezso Nov 1 '14 at 12:37
7

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

  • 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? – Adam Matan Nov 1 '14 at 6:46
  • 1
    @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 – a_horse_with_no_name Nov 1 '14 at 7:48
  • 1
    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! – dwanderson Mar 19 '18 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.