Subject: Re: replacing mysql enum
From: Rod Taylor <pg@rbt.ca>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Ian Barwick <barwick@gmail.com>,
	Kenneth Gonsalves <lawgon@thenilgiris.com>, pgsql-sql@postgresql.org
In-Reply-To: <20041211074227.J30051@megazone.bigpanda.com>
References: <04121110305520.21547@thenilgiris.com>
	<1d581afe04121104255557ea@mail.gmail.com>
	<20041211074227.J30051@megazone.bigpanda.com>
Content-Type: text/plain
Date: Sat, 11 Dec 2004 11:35:01 -0500
Message-Id: <1102782901.26218.173.camel@home>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Sat, 2004-12-11 at 07:47 -0800, Stephan Szabo 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'))

Actually, he just needs check(field in ('a', 'b', 'c')).  NULL is
accepted unless explicitly denied (NOT NULL constraint or an IS NOT NULL
check).

--