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.
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
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;
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.
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.
