Two Postgres tables with same column name and same Index name. Need to drop index on one of them

Question

I have two tables:

x_development=# \d+ xref__email_addresses__organizations
                              Table "public.xref__email_addresses__organizations"
         Column          |            Type             |       Modifiers        | Storage | Stats target | Description 
-------------------------+-----------------------------+------------------------+---------+--------------+-------------
 email_address_id        | integer                     | not null               | plain   |              | 
 rorganization_id        | integer                     | not null               | plain   |              | 
 last_update             | timestamp without time zone | not null default now() | plain   |              | 
Indexes:
    "email_address_id_idx" UNIQUE, btree (email_address_id)
    "xref_idx" UNIQUE, btree (email_address_id, organization_id)
Foreign-key constraints:
    "email_address_id_fkey" FOREIGN KEY (email_address_id) REFERENCES email_addresses(email_address_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "organization_id_fkey" FOREIGN KEY (realtor_organization_id) REFERENCES realtor_organizations(realtor_organization_id) ON UPDATE CASCADE ON DELETE RESTRICT


x_development=# \d+ email_addresses
                                                          Table "public.email_addresses"
       Column       |  Type   |                                 Modifiers                                  | Storage  | Stats target | Description 
--------------------+---------+----------------------------------------------------------------------------+----------+--------------+-------------
 email_address_id   | integer | not null default nextval('email_addresses_email_address_id_seq'::regclass) | plain    |              | 
 email_address      | citext  | not null                                                                   | extended |              | 
 unsubscribe_reason | text    | not null default ''::text                                                  | extended |              | 
Indexes:
    "email_addresses_pkey" PRIMARY KEY, btree (email_address_id)
    "email_address_idx" UNIQUE, btree (email_address)
Referenced by:
    TABLE "xref__email_addresses__organizations" CONSTRAINT "email_address_id_fkey" FOREIGN KEY (email_address_id) REFERENCES email_addresses(email_address_id) ON UPDATE CASCADE ON DELETE RESTRICT

Note they both have an email_address_id column and
"email_address_id_idx" UNIQUE, btree (email_address_id) index.

I need to drop the email_address_id_idx on the xref__email_addresses__organizations table but it seems if mis-created the table by having two identical index names.

How do I drop the email_address_id_idx from the xref__email_addresses__organizations table?

Anand Raja · Accepted Answer · 2018-02-18 16:53:21Z

3

From what i have understood, the indexes are different. One of them is email_address_id_idx and the other is email_address_idx. So I Think you can just use drop index to remove the desired one.

answered Feb 18, 2018 at 16:53

Anand Raja

1115 bronze badges

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

2 Comments

Laurenz Albe Over a year ago

Right, there cannot be two indexes with the same name in the same schema.

riders994 Over a year ago

@LaurenzAlbe There is an interesting edge case. You can have two indexes with the same name in different schemas, in which case DROP INDEX <name> yields a Iindex <name> does not exist, skipping error. In this case, you have to specify the schema in the table name.

Collectives™ on Stack Overflow

Two Postgres tables with same column name and same Index name. Need to drop index on one of them

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related