From: Ian Barwick <barwick@gmail.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: replacing mysql enum
Date: Sat, 11 Dec 2004 18:06:08 +0100
Message-ID: <1d581afe041211090628da57e@mail.gmail.com> (raw)
In-Reply-To: <20041211074227.J30051@megazone.bigpanda.com>
References: <04121110305520.21547@thenilgiris.com>
<1d581afe04121104255557ea@mail.gmail.com>
<20041211074227.J30051@megazone.bigpanda.com>
On Sat, 11 Dec 2004 07:47:51 -0800 (PST), Stephan Szabo
<sszabo@megazone.bigpanda.com> wrote:
> On Sat, 11 Dec 2004, Ian Barwick wrote:
>
> > (Oddly enough, putting the NULL in the CHECK constraint seems
> > to make the constraint worthless:
> > test=> create table consttest (field varchar(2) check (field in
> > (null, 'a','b','c')));
> > CREATE TABLE
> > test=> insert into consttest values ('xx');
> > INSERT 408080 1
> > test=> SELECT * from consttest ;
> > field
> > -------
> > xx
> > (1 row)
> >
> > Not sure what logic is driving this).
>
> The way NULL is handled in IN (because it's effectively an equality
> comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
> never return false and constraints are satisified unless the search
> condition returns false for some row. I think this means you need the
> more verbose (field is null or field in ('a','b','c'))
This works as expected, although for constraints the nullness
of the column is better off handled by applying NOT NULL if
necessary.
What I still don't quite understand is why IN in a CHECK context is
handled differently to say: select 1 where 'x' in (null,'a','b','c') ?
This could be a bit of a gotcha for anyone constructing a constraint
similar to the original poster's and not realising it has no effect.
Ian Barwick
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: pgsql-sql@postgresql.org
Cc: barwick@gmail.com, sszabo@megazone.bigpanda.com, lawgon@thenilgiris.com
Subject: Re: replacing mysql enum
In-Reply-To: <1d581afe041211090628da57e@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox