To: Ian Barwick <barwick@gmail.com>
Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>,
	pgsql-sql@postgresql.org
Subject: Re: replacing mysql enum 
In-reply-to: <1d581afe04121104255557ea@mail.gmail.com> 
References: <04121110305520.21547@thenilgiris.com>
	<1d581afe04121104255557ea@mail.gmail.com>
Comments: In-reply-to Ian Barwick <barwick@gmail.com>
	message dated "Sat, 11 Dec 2004 13:25:37 +0100"
Date: Sat, 11 Dec 2004 12:56:50 -0500
Message-ID: <17433.1102787810@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Ian Barwick <barwick@gmail.com> writes:
> (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

For a non-null field value, that IN clause reduces to
	null OR false OR false OR false
which reduces to null because of the way 3-state boolean logic is
defined in SQL (which makes sense if you interpret null as "unknown").
And a null result from CHECK is defined not to be a failure case by
the SQL standard.

This is really the same logic that allows the explicit-null-free CHECK
condition to accept NULLs:
	null IN ('a','b','c')
becomes
	null OR null OR null
becomes
	null
which doesn't fail.

I believe this was intentional on the part of the SQL committee.  Their
thought was that if you intend to disallow NULLs, you should write an
explicit NOT NULL constraint, separately from any CHECK you might write.
Therefore, defining CHECK such that it tend to fall through silently on
NULL inputs is a good thing.

			regards, tom lane