Add unique constraint to an existing PostgreSQL index

Question

I have an index in my PostgreSQL 9.3 database:

CREATE INDEX index_foos_on_bar_and_baz ON foos USING btree (bar, baz);

(from a schema migration using Ruby on Rails)

The index is present and made things faster. Now that I've cleaned up duplicate foos, I'd like to make this index unique:

CREATE UNIQUE INDEX index_foos_on_bar_and_baz ON foos USING btree (bar, baz);

Is there a way to alter the existing index and make it unique? Or is it easier/faster to delete the existing index and create a new, unique one?

Thanh · Accepted Answer · 2017-09-12 03:41:54Z

23

There is no way to change an index to unique index. You can see what you can change to index at alter index document.

In this case you need to drop and create new unique index.

answered Sep 12, 2017 at 3:41

Thanh

8,6445 gold badges37 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

prayagupadhyay · Accepted Answer · 2020-09-18 04:45:03Z

-4

You can alter an index to add the "UNIQUE" constraint with:

 ALTER TABLE distributors 
 ADD CONSTRAINT dist_id_zipcode_key
 UNIQUE (dist_id, zipcode);

See the official documentation here: https://www.postgresql.org/docs/current/sql-altertable.html

edited Sep 18, 2020 at 4:45

prayagupadhyay

31.4k14 gold badges165 silver badges199 bronze badges

answered Feb 20, 2020 at 6:53

Cold

172 bronze badges

3 Comments

EM0 Over a year ago

How does this alter the existing index? It seems to just create a new unique constraint.

PainIsAMaster Over a year ago

This looks like it creates both the INDEX and UNIQUE constraint on it, pretty cool. Is this expected behavior? Also , the UNIQUE constraint must be dropped to remove the INDEX - you cannot directly remove the INDEX as postgres throws an error because the CONSTRAINT is dependent on the INDEX

M-Razavi Over a year ago

When a unique constraint or primary key is defined for a table, PostgreSQL automatically creates a unique index, but your answer is incorrect for this question because it creates a new unique index, and the table now has two indexes, the first of which is no longer required.

Collectives™ on Stack Overflow

Add unique constraint to an existing PostgreSQL index

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related