10

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?

1
  • 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, 2014 at 12:37

1 Answer 1

11

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

5
  • 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, 2014 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 Nov 1, 2014 at 7:48
  • 2
    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, 2018 at 21:18
  • What if my index has an expression? Then I need to specify the columns for the constraint right? Jan 28 at 14:14
  • 1
    @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 Apr 11 at 2:39

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.