From: | Don Baccus <dhogaza(at)pacifier(dot)com> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | check contraints incorrectly reject "null" |
Date: | 1999-12-09 16:00:50 |
Message-ID: | 3.0.1.32.19991209080050.00e553f0@mail.pacifier.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
From | Date | Subject | |
---|---|---|---|
Next Message | Assaf Arkin | 1999-12-09 18:42:43 | Re: [INTERFACES] Transaction support in 6.5.3/JDBC |
Previous Message | Don Baccus | 1999-12-09 15:57:28 | Re: [HACKERS] memory dilemma |