From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stuart Campbell <stuart.campbell@ridewithvia.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-general@postgresql.org
Subject: Re: Unexpected modification of check constraint definition
In-reply-to: <CAAZ6SnxaG7zCkmcktjULXRWJM_ZVCdqsdhnyZQWsWCx_uVeisw@mail.gmail.com>
References: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com> <1fa24f41862f214b44bcf94556db51c9946d28bd.camel@cybertec.at> <CAAZ6SnxaG7zCkmcktjULXRWJM_ZVCdqsdhnyZQWsWCx_uVeisw@mail.gmail.com>
Comments: In-reply-to Stuart Campbell <stuart.campbell@ridewithvia.com>
	message dated "Thu, 08 Jan 2026 08:55:50 +1100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1857029.1767824641.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 07 Jan 2026 17:24:01 -0500
Message-ID: <1857030.1767824641@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1857030.1767824641%40sss.pgh.pa.us>
Precedence: bulk

Stuart Campbell <stuart.campbell@ridewithvia.com> writes:
> On Wed, Jan 7, 2026 at 11:57 PM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>> That implicit cast is made explicit when the parsed binary form of the
>> constraint expression is
>> reverse engineered to a string during "pg_dump".

> Makes sense.

For context, in pg_dump we are quite concerned that the dumped form of
the expression be interpreted the same way when reloaded, so that's
why implicit casts get made explicit.  (The worry is mainly that the
destination server could have a different set of available operators or
casting rules, so it might opt for some different interpretation of
an underspecified expression.)  This isn't a perfect rule, because the
parser may behave subtly differently when faced with different input,
but it's the best we've been able to do.

> That seems reasonable. What mostly seemed unexpected was that the parsed
> expression changed the second time. i.e. original expression -> rewritten
> expression with explicit type casting -> rewritten expression with (even
> more!) explicit type casting.

Yeah, this is actually a pretty complicated case, because the parser
decides that it ought to convert the IN to an "= ANY(ARRAY)"
construct.  That opens up the question of whether to cast at the level
of the individual array elements, or at the level of the array as a
whole.  The heuristics for that get affected by what casts were in the
original input.

			regards, tom lane