111

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.

Lucas
  • 17,277
  • 5
  • 45
  • 40
Sergey Potapov
  • 3,819
  • 3
  • 27
  • 46

4 Answers4

158

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

Thorkil Værge
  • 2,727
  • 5
  • 32
  • 48
  • 4
    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
  • 4
    @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)? – Parker Aug 31 '18 at 14:43
  • 1
    Does not work - says that constraint does not exists. – niedomnie Apr 01 '21 at 12:17
54

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;
Parker
  • 7,244
  • 12
  • 70
  • 92
  • 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 – Parker 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. – Parker 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). – Parker May 11 '21 at 17:25
7

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.

dcsohl
  • 7,186
  • 1
  • 26
  • 44
1

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.

tigerP
  • 29
  • 2