Re: replacing mysql enum

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ian Barwick <barwick(at)gmail(dot)com>
Cc: Kenneth Gonsalves <lawgon(at)thenilgiris(dot)com>,pgsql-sql(at)postgresql(dot)org
Subject: Re: replacing mysql enum
Date: 2004-12-11 17:56:50
Message-ID: 17433.1102787810@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-sql

Ian Barwick <barwick(at)gmail(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2004-12-11 18:16:25 Re: replacing mysql enum
Previous Message Greg Stark 2004-12-11 17:25:50 Re: replacing mysql enum