From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marcelo Lauxen <marcelolauxen16@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: =?UTF-8?Q?Re:_pg=5Fget=5Findexdef()_output_not_idempotent_for_par?=
         =?UTF-8?Q?tial_indexes_with_ALL(ARRAY[=E2=80=A6])::text[]?=
In-reply-to: 
 <CANKisB3AG6JK1tCMp4myj75tF3t93pEK_ojECdJ=Fnin-E+J_A@mail.gmail.com>
References: 
 <CANKisB3AG6JK1tCMp4myj75tF3t93pEK_ojECdJ=Fnin-E+J_A@mail.gmail.com>
Comments: In-reply-to Marcelo Lauxen <marcelolauxen16@gmail.com>
	message dated "Tue, 12 May 2026 15:03:14 -0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <165552.1778680299.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 13 May 2026 09:51:39 -0400
Message-ID: <165553.1778680299@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/165553.1778680299%40sss.pgh.pa.us>
Precedence: bulk

Marcelo Lauxen <marcelolauxen16@gmail.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