CODEWITHSUNDEEP
postgresqlindexing
RalphShnelvar
RalphShnelvar

Reputation: 607

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

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?

Upvotes: 0

Views: 530

Answers (1)

Anand Raja
Anand Raja

Reputation: 111

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.

Upvotes: 3

Related Questions