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

Gotcha, it wasn't clear to me that this was never guaranteed. I will change
the type of this column to text to resolve this.

Appreciate the quick response!

Regards,
Marcelo

On Wed, May 13, 2026 at 10:51 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>