Re: pg_attribute.attname inconsistency when renaming primary key columns

On Wed, Feb 24, 2021 at 04:55:11PM +0900, Kyotaro Horiguchi wrote:
> 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.

Yes, the attname associated to the index expressions makes that
weird, so you should not rely on that. This reminds me of the
discussion that introduced ALTER INDEX SET STATISTICS, which uses
column numbers:
https://www.postgresql.org/message-id/CAPpHfdsSYo6xpt0F=ngAdqMPFJJhC7zApde9h1qwkdpHpwFisA@mail.gmail.com

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

FWIW, for any schema diff tool, I would recommend to completely ignore
attname, and instead extract the index attributes using
pg_get_indexdef() that can work on attribute numbers. You can find a
lot of inspiration from psql -E to see the queries used internally for
things like \d or \di. For example:
=# create table aa (a int);
=# create index aai on aa((a + a), (a - a));
=# SELECT attnum,
pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'aai' ::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
attnum | indexdef
--------+----------
1 | (a + a)
2 | (a - a)
(2 rows)
=# ALTER TABLE aa RENAME COLUMN a to b;
=# SELECT attnum,
pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'aai' ::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
attnum | indexdef
--------+----------
1 | (b + b)
2 | (b - b)
(2 rows)
--
Michael