Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

Next:From: Josh BerkusDate: 2004-12-11 18:16:25
Subject: Re: replacing mysql enum
Previous:From: Greg StarkDate: 2004-12-11 17:25:50
Subject: Re: replacing mysql enum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group