How to Retrieve Indexed Column Information in PostgreSQL?

Getting Indexed Column Information in PostgreSQL

In MySQL, the SHOW INDEXES command can be used to retrieve the columns that are indexed for a specific table. However, in PostgreSQL, there is no direct equivalent to this command.

To obtain this information in PostgreSQL, you can use the following query:

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname LIKE 'test%';

This query joins the pg_class table (which contains information about tables), the pg_index table (which contains information about indexes), and the pg_attribute table (which contains information about table columns). It then filters the results to only include indexes for a specific table (in this example, tables whose names start with "test").

The output of this query will be a list of index names and the columns that they cover.

To summarize, while MySQL has a dedicated command for retrieving indexed column information, PostgreSQL requires a more complex query involving multiple table joins. Nonetheless, the query provided can accomplish the task effectively.

The above is the detailed content of How to Retrieve Indexed Column Information in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!