Position of the column in the index

Question

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.

score 4 · Accepted Answer · edited Sep 26 '17 at 20:58

4

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.

edited Sep 26 '17 at 20:58

Logan

1,614
1
14
27

answered Sep 09 '11 at 13:59

Mike Sherrill 'Cat Recall'

91,602
17
122
185

But it makes a lot more sense to look at something named "indkey" than it does to join pg_class and pg_attribute. I hate system tables. Every time I look at them I feel like my name's Alice, and I just stepped through the looking glass. – Mike Sherrill 'Cat Recall' Sep 09 '11 at 14:36
I think this is wrong and you must use pg_index.indkey. The query returns attnum which is the order the column appeared in the CREATE TABLE statement. This query will incorrectly handle: `CREATE TABLE foo (a int, b int, PRIMARY KEY (b, a));`. b appears first in the PK but second in the create table statement. Use pensnarik's answer. – Joe Jul 24 '21 at 04:39

score 2 · Answer 2 · edited Sep 27 '17 at 01:10

2

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

edited Sep 27 '17 at 01:10

Logan

1,614
1
14
27

answered Jul 26 '12 at 17:15

Ricardo

21
1

Ricardo's answer is indeed the answer I needed. You need to find the position of pg_attribute.attnum (the position of the column in the table) in the vector pg_index.indkey. This gives you the position of the column in the index. – Jan Dirk Zijlstra Jan 24 '14 at 15:04
Mike's solution is correct when you query the `attnum` of the columns on the index which has its own table. Mike's lets you query by index, this lets you query by table. – Logan Sep 26 '17 at 19:57

score 1 · Answer 3 · answered Mar 02 '20 at 11:01

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;

score 1 · Answer 4 · answered Sep 09 '11 at 13:52

1

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.

answered Sep 09 '11 at 13:52

@Catcall From a quick test, this solutions suitable for me. I have to some tweaking to this to use it. – vchitta Sep 10 '11 at 08:04

Position of the column in the index

4 Answers4

Linked