Thread

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

   Marcelo Lauxen <marcelolauxen16@gmail.com> — 2026-05-12T18:03:14Z

   *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).
   
   *Reproduction:*
   
   > -- Setup
   > CREATE TABLE test_idempotent (
   >   id bigint PRIMARY KEY,
   >   state varchar NOT NULL,
   >   space_id bigint
   > );
   >
   > -- Step 1: Create index using NOT IN
   > CREATE UNIQUE INDEX idx_original ON test_idempotent (space_id)
   >   WHERE state NOT IN ('completed', 'failed', 'cancelled')
   >     AND space_id IS NOT NULL;
   >
   > -- Step 2: Capture pg_get_indexdef output
   > SELECT pg_get_indexdef(indexrelid) AS pass_1
   >   FROM pg_stat_user_indexes
   >   WHERE indexrelname = 'idx_original';
   >
   > -- pass_1 result:
   > -- CREATE UNIQUE INDEX idx_original ON public.test_idempotent USING btree
   > (space_id)
   > --   WHERE (((state)::text <> ALL ((ARRAY['completed'::character varying,
   > --     'failed'::character varying, 'cancelled'::character
   > varying])::text[]))
   > --     AND (space_id IS NOT NULL))
   >
   > -- Step 3: Feed pass_1 output back as a new index
   > CREATE UNIQUE INDEX idx_round_trip ON public.test_idempotent USING btree
   > (space_id)
   >   WHERE (((state)::text <> ALL ((ARRAY['completed'::character varying,
   >     'failed'::character varying, 'cancelled'::character varying])::text[]))
   >     AND (space_id IS NOT NULL));
   >
   > -- Step 4: Compare both
   > SELECT indexrelname, pg_get_indexdef(indexrelid)
   >   FROM pg_stat_user_indexes
   >   WHERE indexrelname IN ('idx_original', 'idx_round_trip')
   >   ORDER BY indexrelname;
   >
   > -- Cleanup
   > DROP TABLE test_idempotent;
   
   
   *Observed result (step 4):*
   
   > idx_original   | ... WHERE (((state)::text <> ALL
   > ((ARRAY['completed'::character varying, 'failed'::character varying,
   > 'cancelled'::character varying])::text[])) AND (space_id IS NOT NULL))
   
    idx_round_trip | ... WHERE (((state)::text <> ALL
   > (ARRAY[('completed'::character varying)::text, ('failed'::character
   > varying)::text, ('cancelled'::character varying)::text])) AND (space_id IS
   > NOT NULL))
   
   
   The array-level cast (ARRAY[...])::text[] is redistributed to per-element
   casts ARRAY[(...::text), (...::text), (...::text)] after a single
   parse→deparse cycle.
   
   *Expected result:*
   
   Both indexes should produce identical pg_get_indexdef() output since
   idx_round_trip was created using the exact SQL that pg_get_indexdef()
   produced for idx_original.
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2026-05-13T13:51:39Z

   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
   
   
   
   

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

   Marcelo Lauxen <marcelolauxen16@gmail.com> — 2026-05-13T14:25:47Z

   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@sss.pgh.pa.us> wrote:
   
   > 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
   >