PostgreSQL 9.3 – Add Unique Constraint Using Existing Index

indexpostgresql-9.3unique-constraint

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?

Best Answer

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

Related Solutions

PostgreSQL duplicate key violates unique constraint error

(I try to compile an answer from the comments.)

It looks like there is a problem in the process with which your application generates the value for the primary key. Generally it is wiser to leave this to the DBMS: define a sequence and leave it fill the PK values. If you have a sequence, you have at least to options: either set the value like

$eventid = exec(SELECT nextval('your_sequence')) // pseudocode!

and use this in the INSERT statement, or completely leave it to PostgreSQL by omitting PK from the INSERT:

INSERT INTO events (source, object, [...]) 
VALUES (2, 3, [...]);

(Personally I usually opt for the second solution.)

You can change to this behaviour by setting the data type of your PK column to bigserial (in case of an integer, it would be just serial):

ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;

This will raise a notice which tells you that a sequence called (probably) events_eventid_seq is generated. You can use it as described above.

EDIT (2012-11-27)

You can set the sequence by a query like

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

In order to let the sequence produce the next value for eventid, you must not set a value explicitly in your INSERT. If you do, it will take the manually supplied value and won't advance the sequence. This will cause the same unique violation error later.

Regarding ALTER TABLE: as you could see, there is some discrepancy between ALTER TABLE - type does not exists and CREATE TABLE. Are you sure you try this on the same database? I can't remember the older versions, but already 8.3 had the bigserial shorthand (it's not a real type). (Giving it further thought, it is quite possible you can't use it in ALTER TABLE, since it is a shorthand...) You can solve the problem by following the manual:

CREATE SEQUENCE events_eventid_seq;

ALTER TABLE events 
ALTER COLUMN eventid TYPE bigint;

ALTER SEQUENCE events_eventid_seq OWNED BY events.eventid;

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

Oracle Index – Using Index on Unique Constraint

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error.

http://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52225

If you specify schema.index, then Oracle attempts to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, then Oracle
returns an error.

If you specify the create_index_statement, then Oracle attempts to create the index and use it to enforce the constraint. If Oracle
cannot create the index or cannot use the index to enforce the
constraint, then Oracle returns an error.

If you neither specify an existing index nor create a new index, then Oracle creates the index. In this case:

The index receives the same name as the constraint.

If table is partitioned, then you can specify a locally or globally partitioned index for the unique or primary key constraint.

Best Answer

Related Solutions

PostgreSQL duplicate key violates unique constraint error

EDIT (2012-11-27)

Oracle Index – Using Index on Unique Constraint

Related Question