Cannot drop index because unique constraint requires it (postgres)

Question

Running Postgres 9.6.

I'm trying to write database migration code to drop a redundant unique constraint and index. The problem is that in some installations these index and constraint exist, and in others they don't.

The recommended way to drop would be:

alter table <table_name> drop constraint <unique_name>

But that fails when the constraint does not exist, and there is no "if exists" clause for this. There is an "if exists" clause to the "drop index" command, but that initially fails as well:

db=> drop index if exists <unique_name>;
ERROR:  cannot drop index <unique_name> because constraint <unique_name> on table <table_name> requires it

But wait, "drop index" has a "cascade" option to remove dependent objects, so we can use that!

Well, no. I get the same response:

db=> drop index if exists <unique_name> cascade;
ERROR:  cannot drop index <unique_name> because constraint <unique_name> on table <table_name> requires it

Note: I've seen a lot of seemingly-related answers where "cascade" solved the problem for people, but these all mention foreign-key constraints, not unique constraints.

Note: This does not relate to the fact that cascade is not supported when trying to remove the index concurrently; when you add the "concurrently" keyword, you get a very explicit

ERROR:  DROP INDEX CONCURRENTLY does not support CASCADE

Is this a known bug? Or am I missing something?

Laurenz Albe · Accepted Answer · 2019-09-02 09:34:19Z

13

You can use a DO statement and trap the error:

DO
$$BEGIN
   ALTER TABLE <table_name> DROP CONSTRAINT <unique_name>;
EXCEPTION
   WHEN undefined_object
      THEN NULL;  -- ignore the error
END$$;

answered Sep 2, 2019 at 9:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Austin Over a year ago

absolute life saver

Collectives™ on Stack Overflow

Cannot drop index because unique constraint requires it (postgres)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related