Given a table definition like:

create table foo (i integer check (i > 0));

I noticed the following works in Oracle but fails in Postgres:

insert into foo values(null);

I was curious about what the standard might say, and had been
meaning to buy Date's book for some time, so broke down and
did so.

According to Date, a check contraint should fail if the expression
evaluates to false.  It appears that Postgres only passes the
check constraint if it evaluates to true.  In three-valued logic,
these statements aren't equivalent.  He has a paragraph about
nulls and check contraints in chapter 14, I believe, and his
explanation makes it clear that Oracle is right, Postgres wrong.

It's easy to fix by adding a check for null to the constraint,
and afterwards the SQL still works with Oracle, but it's still
a bug...



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 09 Dec 1999 08:00:50 -0800
Don Baccus <dhogaza@pacifier.com>
check contraints incorrectly reject "null"
Don Baccus <dhogaza@pacifier.com> writes:
> According to Date, a check contraint should fail if the expression
> evaluates to false.

And SQL92 says:

         A table check constraint is satisfied if and only if the specified
         <search condition> is not false for any row of a table.
                               ^^^^^^^^^

so they agree: a constraint that yields NULL should be considered
to pass.  A tad nonintuitive, but who am I to argue...

I have fixed several bugs recently having to do with incorrect
evaluation of three-state boolean logic.  I'll take care of this one.

			regards, tom lane

Thu, 09 Dec 1999 19:36:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] check contraints incorrectly reject "null" 