CODEWITHSUNDEEP
sqldatabasepostgresqlindexingdatabase-metadata
Joe DiNottra
Joe DiNottra

Reputation: 1008

List Members of an Index in PostgreSQL

I've search here in StackOverflow and other sites, but I'm still not able to list the members of an index. The simplified case is:

CREATE TABLE employee (
  id INT PRIMARY KEY NOT NULL,
  name VARCHAR(20),
  salary INT NOT NULL,
  bonus INT NOT NULL
);

CREATE UNIQUE INDEX idx_employee_1 ON employee (name);

CREATE INDEX idx_employee_2 ON employee ((salary + bonus) desc, name);

How can I get the list of indexes with their members as shown below?

table_name  index_name      is_unique  ordinal  member          is_ascending
----------  --------------  ---------  -------  --------------  ------------
employee    idx_employee_1  true             1  name            true
employee    idx_employee_2  false            1  salary + bonus  false
employee    idx_employee_2  false            2  name            true

Upvotes: 1

Views: 58

Answers (1)

klin
klin

Reputation: 121604

Use pg_indexes (simpler) or pg_index (more informative).

select *
from pg_indexes
where tablename = 'employee'

Db<>fiddle.

Read about pg_indexes and pg_index in the docs.

Upvotes: 2

Related Questions