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.

score 150 · Accepted Answer · edited Sep 28 '19 at 06:51

150

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

edited Sep 28 '19 at 06:51

Thorkil Værge

1,379
3
19
32

answered Aug 29 '13 at 15:28

3

Thanks for the suggestion. Unfortunately there are no such constraints. – Sergey Potapov Aug 29 '13 at 15:29
1

Yeah, I thought of this too, but checked it in Postgres... adding a unique index does not add a constraint to the table... it seems to be part of the index itself. – dcsohl Aug 29 '13 at 16:05
1

@dcsohl Yep. This is interesting, I looked into it and according to [this question](http://stackoverflow.com/questions/6239657/postgresql-can-you-create-an-index-in-the-create-table-definition#6239678) unique constraints can create indexes (but not the other way around) – Aug 29 '13 at 16:11
2

@SergeyPotapov This answer does not solve the problem posed by your question. Could you revisit this question and select the [correct answer](https://stackoverflow.com/a/38488663/2074605)? – vallismortis Aug 31 '18 at 14:43
1

Does not work - says that constraint does not exists. – niedomnie Apr 01 '21 at 12:17

vallismortis · Answer 2 · 2019-01-14T14:45:35.683

48

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;

edited Jan 14 '19 at 14:45

answered Jul 20 '16 at 18:55

vallismortis

6,682
12
70
85

Does not work either. Index "does not exists" but cannot be created again because "relation "" already exists: – niedomnie Apr 01 '21 at 12:19
@niedomnie That is a different problem. You need to check your schema for other relations (e.g. table names) with the same name. See related topic https://stackoverflow.com/a/8835441/2074605 – vallismortis Apr 01 '21 at 13:21
will constraint occupy memory ? – KIRAN KUMAR MATAM May 11 '21 at 13:31
@KIRANKUMARMATAM No, adding or removing a constraint will not have any direct, measurable impact on memory consumption in PostgreSQL. The only exception might be [deferred constraints](http://dbadailystuff.com/deferred-constraints-in-postgresql), which are applied at the end of a transaction, and that would be entirely dependent on your particular database. – vallismortis May 11 '21 at 14:01
@vallismortis thanks for reply what i meant here is space in db ? – KIRAN KUMAR MATAM May 11 '21 at 16:38
@KIRANKUMARMATAM That depends entirely on your schema and the amount of data you have. You can check the sizes using [\di+ tbl*](https://stackoverflow.com/a/46473305/2074605). – vallismortis May 11 '21 at 17:25

score 7 · Answer 3 · answered Aug 29 '13 at 15:25

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.

score 2 · Answer 4 · answered Feb 28 '20 at 11:19

2

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.

answered Feb 28 '20 at 11:19

tigerP

31
2

2

This seems not to be related to the question which asked for changing an index. – GreenTurtle Feb 28 '20 at 11:38

score 0 · Answer 5 · answered Aug 10 '21 at 15:02

0

this worked for me, no need to specify table since the index is unique in your case:

DROP INDEX if exists my_index_name;

answered Aug 10 '21 at 15:02

Uma

846
5
7

Subham kuswa · Answer 6 · 2020-12-17T06:29:00.463

If you have PgAdmin4 installed than it's very easy just follow below steps...

Go to table properties(right click on table and select properties).
A window will open than navigate to Constraints in properties window.
In Constraints option you will see options as below screenshot just go to Unique there you will see columns that has unique constraints. Click on delete button and save. that's it you are good to go.

Remove uniqueness of index in PostgreSQL

6 Answers6

Linked