CODEWITHSUNDEEP
sqlpostgresql
SteamedBun
SteamedBun

Reputation: 91

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

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?

Upvotes: 1

Views: 2173

Answers (1)

dmg
dmg

Reputation: 4481

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;

Upvotes: 3

Related Questions