Position of the column in the index

How to get the position of a column in the index in PostgreSQL database? 开发者_运维百科indkey in pg_index catalog table gives the position of that column in it's table, but i want the position of the column in it's containing index.

Here's one way. It might not be the best way.

SELECT c.relname, a.attname, a.attnum 
FROM pg_attribute a
INNER JOIN pg_class c on c.oid = a.attrelid 
WHERE c.relkind = 'i'
  AND c.relname = 'beds_pkey'
  AND a.attnum > 0

where beds_pkey is the name of the index.

Here is a query that retrieves the position on the index when you search by table:

select
  c.relname as tabela,
  a.relname as indexname,
  d.attname as coluna,
  (
    select
      temp.i + 1
    from
      (
        SELECT generate_series(array_lower(b.indkey,1),array_upper(b.indkey,1)) as i
      ) temp
    where
      b.indkey[i] = d.attnum
  ) as posicao
from
  pg_class a
    inner join
  pg_index b
    on
  a.oid = b.indexrelid 
    inner join
  pg_class c
    on
  b.indrelid = c.oid
    inner join
  pg_attribute d
    on
  c.oid = d.attrelid and
  d.attnum = any(b.indkey)
where
  b.indisprimary != true and
  a.relname not like 'pg_%'
order by
  tabela, indexname, posicao

indkey is an array and the order of the entries in that array determine the order of the index columns.

So if indkey contains {2,4} then the second column of the table comes first in the index, and the table's fourth column is the second column in the index.

If indkey contains {4,3} then the table's fourth column is the first column in the index and the table's third column is the index' second column.

Just use array_position() function to get desired column index within indkey array:

select c.relname, a.attname,
       array_position(i.indkey, a.attnum)
  from pg_index i
  join pg_class c
    on c.oid = i.indexrelid
   and c.relkind = 'i'
  join pg_attribute a
    on a.attrelid = 'your_table'::regclass::oid
   and a.attnum = any(i.indkey)
 where indrelid = 'your_table'::regclass::oid
 order by 1, 2;