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?

Answer 1

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.