Re: Unexpected modification of check constraint definition

On Wed, 2026-01-07 at 21:32 +1100, Stuart Campbell wrote:
> I noticed that check constraint definitions are sometimes rewritten/normalized on input, and I was hoping to
understandthat 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'::charactervarying])::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?)

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 constraint 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 artificial example:

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

  SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'con';

          pg_get_constraintdef
  ════════════════════════════════════
   CHECK (((col)::text <> 'y'::text))

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

  SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'con';

                     pg_get_constraintdef
  ══════════════════════════════════════════════════════════
   CHECK (((col)::pg_catalog.text <> 'y'::pg_catalog.text))

The proper solution is not to rely on the way that such expressions are rendered.
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 "text"
early: CHECK (val::text IN ('a','b','c'))

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

Yours,
Laurenz Albe