On Wed, Feb 24, 2021 at 04:55:11PM +0900, Kyotaro Horiguchi wrote: > At Mon, 22 Feb 2021 21:42:44 +0100, "Joel Jacobson" <j...@compiler.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=ngadqmpfjjhc7zapde9h1qwkdphpwf...@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
signature.asc
Description: PGP signature
