Message-ID: <1fa24f41862f214b44bcf94556db51c9946d28bd.camel@cybertec.at>
Subject: Re: Unexpected modification of check constraint definition
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Stuart Campbell <stuart.campbell@ridewithvia.com>, 
	pgsql-general@postgresql.org
Date: Wed, 07 Jan 2026 13:57:49 +0100
In-Reply-To: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com>
References: 
	<CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.2 (3.58.2-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/1fa24f41862f214b44bcf94556db51c9946d28bd.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-01-07 at 21:32 +1100, Stuart Campbell wrote:
> I noticed that check constraint definitions are sometimes rewritten/norma=
lized on input, and I was hoping to understand that a little better.
>=20
> For instance, if I create this table with a check constraint:
>=20
> create table foo (
> =C2=A0 val varchar,
> =C2=A0 constraint val_valid check (val in ('a','b','c'))
> );
>=20
> and then dump the schema with pg_dump, it looks more like this:
>=20
> CREATE TABLE public.foo (
> =C2=A0 =C2=A0 val character varying,
> =C2=A0 =C2=A0 CONSTRAINT val_valid CHECK (((val)::text =3D ANY ((ARRAY['a=
'::character varying, 'b'::character varying, 'c'::character varying])::tex=
t[])))
> );
>=20
> However, if I then recreate the schema from that dump, and then dump with=
 pg_dump a second time, it ends up different again:
>=20
> CREATE TABLE public.foo (
> =C2=A0 =C2=A0 val character varying,
> =C2=A0 =C2=A0 CONSTRAINT val_valid CHECK (((val)::text =3D ANY (ARRAY[('a=
'::character varying)::text, ('b'::character varying)::text, ('c'::characte=
r varying)::text])))
> );
>=20
> I'm working in a Ruby on Rails application where the schema is periodical=
ly 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)
>=20
> Is it expected that the second form is=C2=A0rewritten 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. (Ma=
ybe this is an issue with
> using varchar instead of text?)

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".
That implicit cast is made explicit when the parsed binary form of the cons=
traint expression is
reverse engineered to a string during "pg_dump".

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.  Look at this art=
ificial example:

  CREATE TABLE test (col varchar CONSTRAINT con CHECK (col <> 'y'));

  SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname =3D 'co=
n';

          pg_get_constraintdef       =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90
   CHECK (((col)::text <> 'y'::text))

  CREATE TYPE public.text AS enum ('x');
  SET search_path =3D public, pg_catalog;

  SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname =3D 'co=
n';

                     pg_get_constraintdef                  =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
   CHECK (((col)::pg_catalog.text <> 'y'::pg_catalog.text))

The proper solution is not to rely on the way that such expressions are ren=
dered.
Rather than relying on a tool like pg_dump to create your "structure.sql", =
write it
by hand an maintain in in a version control system.

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

> This communication and any attachments may contain confidential informati=
on and are intended to be
> viewed only by the intended recipients.

Got it, I won't forward your mail... err...

Yours,
Laurenz Albe