Edit: PostgreSQL 10 was breaking the column order due to a wrong SELECT statement, fixed query:
SELECT
i.relname AS index_name,
(
SELECT array_agg(attname)
FROM (
SELECT a.attname
FROM (
SELECT colnum, row_number() over() AS rownum
FROM (
SELECT unnest(string_to_array(ix.indkey::text, ' ')) AS colnum
) AS c
) AS b, pg_attribute a
WHERE a.attrelid = t.oid AND a.attnum = b.colnum::int
ORDER BY b.rownum
) AS d
) AS column_names
FROM pg_catalog.pg_class t
JOIN pg_catalog.pg_index ix ON ix.indrelid = t.oid
JOIN pg_catalog.pg_class i ON i.oid = ix.indexrelid
WHERE
t.relkind = 'r'
AND t.relnamespace = to_regnamespace('public')
AND t.relname = 'users'
;
Gah, I'm not proud of this one...
Original post:
In the whole catalog the only place I could find where the index columns can be read in index order is in the pg_index.indkey column.
Problem lies in the fact it's an int2vector type column, which cannot be converted to array. Only way to go is to string split the space-separated output of the value using string_to_array().
You need then to unnest the result and join with pg_attribute to get the column names.
Consider the following table:
test_db=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
org_id | integer | | |
name | text | | |
username | character varying(255) | | | NULL::character varying
email | character varying(255) | | not null |
date | timestamp with time zone | | | CURRENT_TIMESTAMP
no_constraint_col | text | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_key" UNIQUE CONSTRAINT, btree (email)
Foreign-key constraints:
"users_org_id" FOREIGN KEY (org_id) REFERENCES org(id) ON DELETE CASCADE
Referenced by:
TABLE "user_address" CONSTRAINT "user_address_user_id_fk" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
Then let's add an additional index for demonstration purpose:
test_db=# create index on users (id, name, date);
Then, the following catalog query would do the trick:
SELECT
i.relname AS index_name,
(
SELECT array_agg(a.attname)
FROM (SELECT t.oid, unnest(string_to_array(ix.indkey::text, ' ')) AS colnum) AS b
JOIN pg_attribute a ON
a.attrelid = b.oid AND a.attnum = b.colnum::int
) AS column_names
FROM pg_catalog.pg_class t
JOIN pg_catalog.pg_index ix ON ix.indrelid = t.oid
JOIN pg_catalog.pg_class i ON i.oid = ix.indexrelid
WHERE
t.relkind = 'r'
AND t.relnamespace = to_regnamespace('public')
AND t.relname = 'users'
;
Which will output:
index_name | column_names
------------------------+----------------
users_id_name_date_idx | {id,name,date}
users_pkey | {id}
users_email_key | {email}
(3 rows)
Took me a while to get it right.
INFORMATION_SCHEMA.STATISTICSis specific to MySQL, not SQL Server. – Owing