public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Stuart Campbell <[email protected]>
To: [email protected]
Subject: Re: Unexpected modification of check constraint definition
Date: Wed, 07 Jan 2026 13:57:49 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com>
References: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com>

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 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?)

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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Unexpected modification of check constraint definition
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox