public inbox for [email protected]  

help / color / mirror / Atom feed

From: Stuart Campbell <[email protected]>
To: [email protected]
Subject: Unexpected modification of check constraint definition
Date: Wed, 7 Jan 2026 21:32:56 +1100
Message-ID: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com> (raw)

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.

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]
  Subject: Re: Unexpected modification of check constraint definition
  In-Reply-To: <CAAZ6SnzFLQzraWws7_ZKjGtJ+XNK+Hz9DStGbEPzzHKjUXqELQ@mail.gmail.com>

* 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