MIME-Version: 1.0
From: Stuart Campbell <stuart.campbell@ridewithvia.com>
Date: Wed, 7 Jan 2026 21:32:56 +1100
Message-ID: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com>
Subject: Unexpected modification of check constraint definition
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006470030647c9d154"
Archived-At: <https://www.postgresql.org/message-id/CAAZ6SnzFLQzraWws7_ZKjGtJ%2BXNK%2BHz9DStGbEPzzHKjUXqELQ%40mail.gmail.com>
Precedence: bulk

--0000000000006470030647c9d154
Content-Type: text/plain; charset="UTF-8"

Hi there,

I noticed that check constraint definitions are sometimes
rewritten/normalized on input, and I was hoping to understand that a little
better.

For instance, if I create this table with a check constraint:

create table foo (
  val varchar,
  constraint val_valid check (val in ('a','b','c'))
);

and then dump the schema with pg_dump, it looks more like this:

CREATE TABLE public.foo (
    val character varying,
    CONSTRAINT val_valid CHECK (((val)::text = ANY ((ARRAY['a'::character
varying, 'b'::character varying, 'c'::character varying])::text[])))
);

However, if I then recreate the schema from that dump, and then dump with
pg_dump a second time, it ends up different again:

CREATE TABLE public.foo (
    val character varying,
    CONSTRAINT val_valid CHECK (((val)::text = ANY (ARRAY[('a'::character
varying)::text, ('b'::character varying)::text, ('c'::character
varying)::text])))
);

I'm working in a Ruby on Rails application where the schema is periodically
dumped to a structure.sql file on disk. So, it would be convenient if the
constraint definition was "stable" (otherwise, there's unnecessary noise in
our version control history)

Is it expected that the second form is rewritten into the third form? It
seems a bit odd to see all the type casting going on, but maybe there is a
good reason for that. (Maybe this is an issue with using varchar instead of
text?)

Regards,
Stuart

-- 
This communication and any attachments may contain confidential information 
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.


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

<div dir=3D"ltr"><div>Hi there,</div><div><br></div><div>I noticed that che=
ck constraint definitions are sometimes rewritten/normalized on input, and =
I was hoping to understand that a little better.</div><div><br></div><div>F=
or instance, if I create this table with a check constraint:</div><div><br>=
</div><div style=3D"margin-left:40px"><span style=3D"font-family:monospace"=
>create table foo (</span></div><div style=3D"margin-left:40px"><span style=
=3D"font-family:monospace">=C2=A0 val varchar,</span></div><div style=3D"ma=
rgin-left:40px"><span style=3D"font-family:monospace">=C2=A0 constraint val=
_valid check (val in (&#39;a&#39;,&#39;b&#39;,&#39;c&#39;))</span></div><di=
v style=3D"margin-left:40px"><span style=3D"font-family:monospace">);</span=
></div><div><br></div><div>and then dump the schema with pg_dump, it looks =
more like this:</div><div><br></div><div><div style=3D"margin-left:40px"><s=
pan style=3D"font-family:monospace">CREATE TABLE public.foo (</span><br><sp=
an style=3D"font-family:monospace">=C2=A0 =C2=A0 val character varying,</sp=
an><br><span style=3D"font-family:monospace">=C2=A0 =C2=A0 CONSTRAINT val_v=
alid CHECK (((val)::text =3D ANY ((ARRAY[&#39;a&#39;::character varying, &#=
39;b&#39;::character varying, &#39;c&#39;::character varying])::text[])))</=
span><br><span style=3D"font-family:monospace">);</span><br></div><br></div=
><div>However, if I then recreate the schema from that dump, and then dump =
with pg_dump a second time, it ends up different again:</div><div><br></div=
><div><div style=3D"margin-left:40px"><span style=3D"font-family:monospace"=
>CREATE TABLE public.foo (</span><br><span style=3D"font-family:monospace">=
=C2=A0 =C2=A0 val character varying,</span><br><span style=3D"font-family:m=
onospace">=C2=A0 =C2=A0 CONSTRAINT val_valid CHECK (((val)::text =3D ANY (A=
RRAY[(&#39;a&#39;::character varying)::text, (&#39;b&#39;::character varyin=
g)::text, (&#39;c&#39;::character varying)::text])))</span><br><span style=
=3D"font-family:monospace">);</span><br></div><br></div><div>I&#39;m workin=
g in a Ruby on Rails application where the schema is periodically dumped to=
 a structure.sql file on disk. So, it would be convenient if the constraint=
 definition was &quot;stable&quot; (otherwise, there&#39;s unnecessary nois=
e in our version control history)</div><div><br></div><div>Is it expected t=
hat the second form is=C2=A0rewritten into the third form? It seems a bit o=
dd to see all the type casting going on, but maybe there is a good reason f=
or that. (Maybe this is an issue with using varchar instead of text?)</div>=
<div><br></div><div>Regards,</div><div>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>
--0000000000006470030647c9d154--