Postgresql: finding the name of an index so it can be dropped using drop index

Question

I was wondering how to drop an index in postgresql. I know to use DROP INDEX, but many of the examples I see online show creating an index and dropping the known index such as this site: https://www.geeksforgeeks.org/postgresql-drop-index/.

However, what I am looking for is going into a table and just dropping already existing indexes. I found this site that tells me how to show the different index names and index defs: https://www.postgresqltutorial.com/postgresql-indexes/postgresql-list-indexes/, but not sure how to drop the indexes from here. Or in other words, how would I get/query an index name from the database, and then drop that index?

I'm quite new to sql and querying in general so I was wondering if the way to do this is to make a new table based on the SELECT/FROM/WHERE and this way I would have access to the index names and defs in a table which I could use to drop them? Could I just replace the SELECT with a DROP INDEX? Is there a better way to do this?

dmg · Accepted Answer · 2022-07-10 23:58:51Z

3

Use \d and \di to find the name of the index, then drop it.

\d <tableName>

lists (among other things) the indexes of the relation.

\di

lists the names of all the indexes in the schema.

once you have the name, simply use:

drop index <name>;

As you can see below, some indexes cannot be dropped, since they are created by constraints (primary key, foreign key constraint, come to my mind).

[local]:dmg@rip=# \d r
                 Table "public.r"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
 b      | integer |           |          | 
Indexes:
    "r_pkey" PRIMARY KEY, btree (a)
    "rindx" btree (a)
Referenced by:
    TABLE "s" CONSTRAINT "s_a_fkey" FOREIGN KEY (a) REFERENCES r(a) ON UPDATE CASCADE

[local]:dmg@rip=# \di
            List of relations
 Schema |  Name  | Type  | Owner | Table 
--------+--------+-------+-------+-------
 public | r_pkey | index | dmg   | r
 public | rindx  | index | dmg   | r
 public | s_pkey | index | dmg   | s
(3 rows)

[local]:dmg@rip=# drop index r_pkey;
ERROR:  cannot drop index r_pkey because constraint r_pkey on table r requires it
HINT:  You can drop constraint r_pkey on table r instead.

[local]:dmg@rip=# drop index rindx;

edited Jul 10, 2022 at 23:58

answered Nov 5, 2021 at 0:08

dmg

4,5892 gold badges21 silver badges27 bronze badges

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

4 Comments

SteamedBun Over a year ago

Is the use of /d and /di only for the command prompt? I'm trying to write a script that uses groovy and PostgreSQL, so I was hoping I could do this without the use of the command prompt.

Laurenz Albe Over a year ago

You can use this query to find the indexes on tab: SELECT indexrelid::regclass FROM pg_index WHERE indrelid = 'tab'::regclass;

SteamedBun Over a year ago

Thanks, I think I can get/find the indexes, but I'm not sure how to drop those indexes? Do I have to save those index values somewhere first and then drop them or can I just straight out drop them?

dmg Over a year ago

you can just drop the index (drop index <name>;). It you needed it again, simple create it. No need to do anything else.

Collectives™ on Stack Overflow

Postgresql: finding the name of an index so it can be dropped using drop index

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related