Movatterモバイル変換

[0]ホーム
URL:

[New] Neon Local Connect for VS Code - Deploy and manage Neon branches locally
PostgreSQL Tutorial
PostgreSQL Tutorial

PostgreSQL List Indexes

Summary: in this tutorial, you will learn how to list indexes from a PostgreSQL database by using eitherpg_indexes view orpsql command.

PostgreSQL does not provide a command likeSHOW INDEXES to list the index information of a table or database.

However, it does provide you with access to thepg_indexes view so that you can query the index information.

If you use thepsql program to interact with the PostgreSQL database, you can use the\d command to view the index information for a table.

PostgreSQL List Indexes using pg_indexes View

Thepg_indexes view allows you to access useful information on each index in the PostgreSQL database.

Thepg_indexes view consists of five columns:

  • schemaname: stores the name of the schema that contains tables and indexes.
  • tablename: indicates the name of the table to which the index belongs.
  • indexname: represents the name of the index.
  • tablespace: identifies the name of the tablespace that contains indexes.
  • indexdef: contains the index definition command in the form ofCREATE INDEX statement.

The following statement lists all indexes of the schemapublic in the current database:

SELECT    tablename,    indexname,    indexdefFROM    pg_indexesWHERE    schemaname = 'public'ORDER BY    tablename,    indexname;

Output:

tablename      |                      indexname                      |                                                                   indexdef--------------------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------- accounts           | accounts_email_key                                  | CREATE UNIQUE INDEX accounts_email_key ON public.accounts USING btree (email) accounts           | accounts_pkey                                       | CREATE UNIQUE INDEX accounts_pkey ON public.accounts USING btree (user_id) accounts           | accounts_username_key                               | CREATE UNIQUE INDEX accounts_username_key ON public.accounts USING btree (username) actor              | actor_pkey                                          | CREATE UNIQUE INDEX actor_pkey ON public.actor USING btree (actor_id) actor              | idx_actor_first_name                                | CREATE INDEX idx_actor_first_name ON public.actor USING btree (first_name) actor              | idx_actor_last_name                                 | CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name)...

To show all the indexes of a table, you use the following statement:

SELECT  indexname,  indexdefFROM  pg_indexesWHERE  tablename= 'table_name';

For example, to list all the indexes for thecustomer table, you use the following statement:

SELECT    indexname,    indexdefFROM    pg_indexesWHERE    tablename = 'customer';

Here is the output:

indexname|                                    indexdef-------------------+-------------------------------------------------------------------------------- customer_pkey| CREATE UNIQUE INDEX customer_pkeyON public.customer USINGbtree(customer_id) idx_fk_address_id| CREATE INDEX idx_fk_address_idON public.customer USINGbtree(address_id) idx_fk_store_id| CREATE INDEX idx_fk_store_idON public.customer USINGbtree(store_id) idx_last_name| CREATE INDEX idx_last_nameON public.customer USINGbtree(last_name)(4 rows)

If you want to get a list of indexes for tables whose names start with the letterc, you can use the following query:

SELECT    tablename,    indexname,    indexdefFROM    pg_indexesWHERE    tablenameLIKE 'c%'ORDER BY    tablename,    indexname;

The following shows the output:

tablename|     indexname|                                      indexdef------------+-------------------+------------------------------------------------------------------------------------ categories| categories_pkey| CREATE UNIQUE INDEX categories_pkeyON public.categories USINGbtree(category_id) category| category_pkey| CREATE UNIQUE INDEX category_pkeyON public.category USINGbtree(category_id) city| city_pkey| CREATE UNIQUE INDEX city_pkeyON public.city USINGbtree(city_id) city| idx_fk_country_id| CREATE INDEX idx_fk_country_idON public.city USINGbtree(country_id) country| country_pkey| CREATE UNIQUE INDEX country_pkeyON public.country USINGbtree(country_id) customer| customer_pkey| CREATE UNIQUE INDEX customer_pkeyON public.customer USINGbtree(customer_id) customer| idx_fk_address_id| CREATE INDEX idx_fk_address_idON public.customer USINGbtree(address_id) customer| idx_fk_store_id| CREATE INDEX idx_fk_store_idON public.customer USINGbtree(store_id) customer| idx_last_name| CREATE INDEX idx_last_nameON public.customer USINGbtree(last_name)(9 rows)

PostgreSQL List Indexes using psql command

If you usepsql to connect to a PostgreSQL database and want to list all indexes of a table, you can use the\d psql command as follows:

\d table_name

The command will return all information about the table including the table’s structure, indexes, constraints, andtriggers.

For example, the following statement returns detailed information about thecustomer table:

\d customer

The output is:

Table "public.customer"   Column    |            Type             | Collation | Nullable |                    Default-------------+-----------------------------+-----------+----------+----------------------------------------------- customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass) store_id    | smallint                    |           | not null | first_name  | character varying(45)|           | not null | last_name   | character varying(45)|           | not null | email       | character varying(50)|           |          | address_id  | smallint                    |           | not null | activebool  | boolean                     |           | not null | true create_date | date                        |           | not null | 'now'::text::date last_update | timestamp without time zone |           |          | now() active      | integer                     |           |          |Indexes:    "customer_pkey" PRIMARY KEY, btree (customer_id)    "idx_fk_address_id" btree (address_id)    "idx_fk_store_id" btree (store_id)    "idx_last_name" btree (last_name)Foreign-key constraints:    "customer_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICTReferenced by:    TABLE "payment" CONSTRAINT "payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT    TABLE "rental" CONSTRAINT "rental_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICTTriggers:    last_updated BEFORE UPDATE ON customer FOR EACH ROW EXECUTE FUNCTION last_updated()

The output shows the index of the table under theIndexes section.

Summary

  • Query data from thepg_indexes view to retrieve the index information.
  • Use the\d table_name command to display the table information along with indexes.

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp