Remove uniqueness of index in PostgreSQL

Question

In my PostgreSQL database I have a unique index created this way:

CREATE UNIQUE INDEX <my_index> ON <my_table> USING btree (my_column)

Is there way to alter the index to remove the unique constraint? I looked at ALTER INDEX documentation but it doesn't seem to do what I need.

I know I can remove the index and create another one, but I'd like to find a better way, if it exists.

Answer 1

You may be able to remove the unique CONSTRAINT, and not the INDEX itself.

Check your CONSTRAINTS via select * from information_schema.table_constraints;

Then if you find one, you should be able to drop it like:

ALTER TABLE <my_table> DROP CONSTRAINT <constraint_name>

Edit: a related issue is described in this question

Answer 2

I don't think it's possible... even in the pgAdmin III UI, if you try to edit a constraint created with your statement, the "Unique" box is greyed-out; you can't change it through the UI. Combined with your research on the ALTER INDEX docs, I'd say it can't be done.

Answer 3

Searched for hours for the same quesiton and doesnt seem to get a right answer---- all the given answers just failed to work.

For not null, it also took me some time to find. Apparently for some reason, the majority-certified codes just dont work when I use it.

I got the not null version code, something like this

ALTER TABLE tablename
ALTER COLUMN column_want_to_remove_constriant
DROP NOT NULL

Sadly changing 'not null' to 'unique' doesnt work.

Answer 4

Assume you have the following:

Indexes:
    "feature_pkey" PRIMARY KEY, btree (id, f_id)
    "feature_unique" UNIQUE, btree (feature, f_class)
    "feature_constraint" UNIQUE CONSTRAINT, btree (feature, f_class)

To drop the UNIQUE CONSTRAINT, you would use ALTER TABLE:

ALTER TABLE feature DROP CONSTRAINT feature_constraint;

To drop the PRIMARY KEY, you would also use ALTER TABLE:

ALTER TABLE feature DROP CONSTRAINT feature_pkey;

To drop the UNIQUE [index], you would use DROP INDEX:

DROP INDEX feature_unique;