From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ian Barwick <barwick@gmail.com>
Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: replacing mysql enum
Date: Sat, 11 Dec 2004 12:56:50 -0500
Message-ID: <17433.1102787810@sss.pgh.pa.us> (raw)
In-Reply-To: <1d581afe04121104255557ea@mail.gmail.com>
References: <04121110305520.21547@thenilgiris.com>
<1d581afe04121104255557ea@mail.gmail.com>
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
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: tgl@sss.pgh.pa.us, barwick@gmail.com, lawgon@thenilgiris.com
Subject: Re: replacing mysql enum
In-Reply-To: <17433.1102787810@sss.pgh.pa.us>
* 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