Re: pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[]

Marcelo Lauxen <marcelolauxen16(at)gmail(dot)com> writes:
> *PostgreSQL version*: 18.3 (Homebrew) on aarch64-apple-darwin24.6.0
> *pg_get_indexdef()* produces SQL that, when executed, yields a different
> pg_get_indexdef() output. This means a pg_dump → pg_restore cycle silently
> changes the deparsed form of partial index WHERE clauses that use NOT IN
> (...) on a varchar column, causing cosmetic drift in tools that compare
> index definitions (e.g. ORM schema dumps, annotation generators).

You are assuming a property that we've never guaranteed and don't plan
to start guaranteeing, ie that the output of expression decompilation
matches the input even in semantically-insignificant details.

My own advice about how to fix this particular example is not to use
varchar --- especially not unconstrained varchar, which doesn't even
have the thin excuse of being spec-compliant. Postgres' native string
type is text.

regards, tom lane