Re: interesting check constraint behavior

On 7 Jan 2010, at 24:12, Gauthier, Dave wrote:

> thedb=# create table foo (col1 text, constraint chk check (col1 in ('a','b','c',null)));
> CREATE TABLE
> thedb=# insert into foo (col1) values ('xxx');
> INSERT 0 1
>
> Hmmmm... I would have thought that this would have violated the constraint because ‘xxx’ is not null and nit one of the allowed values.

As Tom already explained, "value in ('yada', 'yada', null)" evaluates to null. You'll need to make sure your check expression evaluates to either true or false - not null.

I guess you need: check(col1 in ('a', 'b', 'c') or col1 is null)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b45bc2610731598743439!