MIME-Version: 1.0
References: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com>
 <1fa24f41862f214b44bcf94556db51c9946d28bd.camel@cybertec.at>
In-Reply-To: <1fa24f41862f214b44bcf94556db51c9946d28bd.camel@cybertec.at>
From: Stuart Campbell <stuart.campbell@ridewithvia.com>
Date: Thu, 8 Jan 2026 08:55:50 +1100
Message-ID: <CAAZ6SnxaG7zCkmcktjULXRWJM_ZVCdqsdhnyZQWsWCx_uVeisw@mail.gmail.com>
Subject: Re: Unexpected modification of check constraint definition
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a42d1e0647d35b11"
Archived-At: <https://www.postgresql.org/message-id/CAAZ6SnxaG7zCkmcktjULXRWJM_ZVCdqsdhnyZQWsWCx_uVeisw%40mail.gmail.com>
Precedence: bulk

--000000000000a42d1e0647d35b11
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Jan 7, 2026 at 11:57=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> Yes, using "varchar" is definitely part of why it is so odd.
> There is no equality operator for "varchar", so you need an (implicit)
> cast to "text".
>

Got it. That seems like a possible reason to prefer text over varchar.


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


> I'd say that the change you mention "just happened", but you can never
> rely on these expressions
> being rendered in a fixed way - this can change any time.
>

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.


> But perhaps it is good enoulh if you define the constraint by casting to
> "text"
> early: CHECK (val::text IN ('a','b','c'))
>

Sounds good, I may try that. Thanks!


> > This communication and any attachments may contain confidential
> information and are intended to be
> > viewed only by the intended recipients.
>
> Got it, I won't forward your mail... err...
>

Heh, yes... sorry about that. I can't control how that message is included
from this email address. Rest assured, you and everyone else here are the
intended recipients :-)

Regards,
Stuart

--=20
This communication and any attachments may contain confidential information=
=20
and are intended to be viewed only by the intended recipients. If you have=
=20
received this message in error, please notify the sender immediately by=20
replying to the original message and then delete all copies of the email=20
from your systems.


--000000000000a42d1e0647d35b11
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Jan 7, 2026 at 11:57=E2=80=AFPM L=
aurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cy=
bertec.at</a>&gt; wrote:<br></div><div class=3D"gmail_quote gmail_quote_con=
tainer"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex">
Yes, using &quot;varchar&quot; is definitely part of why it is so odd.<br>
There is no equality operator for &quot;varchar&quot;, so you need an (impl=
icit) cast to &quot;text&quot;.<br></blockquote><div><br></div><div>Got it.=
 That seems like a possible reason to prefer text over varchar.</div><div>=
=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
That implicit cast is made explicit when the parsed binary form of the cons=
traint expression is<br>
reverse engineered to a string during &quot;pg_dump&quot;.<br></blockquote>=
<div><br></div><div>Makes sense.</div><div>=C2=A0</div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">
I&#39;d say that the change you mention &quot;just happened&quot;, but you =
can never rely on these expressions<br>
being rendered in a fixed way - this can change any time.<br></blockquote><=
div><br></div><div>That seems reasonable. What mostly seemed unexpected was=
 that the parsed expression changed the second=C2=A0time. i.e. original exp=
ression -&gt; rewritten expression with explicit type casting -&gt; rewritt=
en expression with (even more!) explicit type casting.</div><div>=C2=A0</di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">
But perhaps it is good enoulh if you define the constraint by casting to &q=
uot;text&quot;<br>
early: CHECK (val::text IN (&#39;a&#39;,&#39;b&#39;,&#39;c&#39;))<br></bloc=
kquote><div><br></div><div>Sounds good, I may try that. Thanks!</div><div>=
=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
&gt; This communication and any attachments may contain confidential inform=
ation and are intended to be<br>
&gt; viewed only by the intended recipients.<br>
<br>
Got it, I won&#39;t forward your mail... err...<br></blockquote><div><br></=
div><div>Heh, yes... sorry about that. I can&#39;t control how that message=
 is included from this email address. Rest assured, you and everyone else h=
ere are the intended recipients :-)</div><br></div><div class=3D"gmail_quot=
e gmail_quote_container">Regards,</div><div class=3D"gmail_quote gmail_quot=
e_container">Stuart</div></div>

<br>
<div>This communication and any attachments may contain confidential inform=
ation and are intended to be viewed only by the intended recipients. If you=
 have received this message in error, please notify the sender immediately =
by replying to the original message and then delete all copies of the email=
 from your systems.</div><div><br></div><div><br></div>
--000000000000a42d1e0647d35b11--