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

Marcelo Lauxen <marcelolauxen16@gmail.com>

From: Marcelo Lauxen <marcelolauxen16@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Date: 2026-05-12T18:03:14Z
Lists: pgsql-bugs
*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.