Re: pg_attribute.attname inconsistency when renaming primary key columns

At Mon, 22 Feb 2021 21:42:44 +0100, "Joel Jacobson" <joel(at)compiler(dot)org> wrote in
> I solved my problem by using attnum::text instead of attname for pg_class.relkind = ‘i’ as a work-around to avoid a diff.

For your information, note that the attname of an index relation is
not the name of the target column in the base table. If you created
an index with expression columns, the attributes would be named as
"expr[x]". And the names are freely changeable irrelevantly from the
column names of the base table.

So to know the referred column name of an index column, do something
like the following instead.

SELECT ci.relname as indexname, ai.attname as indcolname,
cr.relname as relname, ar.attname as relattname, ar.attnum
FROM pg_index i
JOIN pg_class cr ON (cr.oid = i.indrelid)
JOIN pg_class ci ON (ci.oid = i.indexrelid)
JOIN pg_attribute ai ON (ai.attrelid = ci.oid)
JOIN pg_attribute ar ON (ar.attrelid = cr.oid AND ar.attnum = ANY(i.indkey))
WHERE ci.relnamespace = 'public'::regnamespace;

indexname | indcolname | relname | relattname | attnum
-----------+------------+---------+------------+--------
bar_pk | foo_id | bar | bar_id | 1
(1 row)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center