Re: interesting check constraint behavior

"Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> writes:
> 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.

Nulls are tricky. That constraint is equivalent to

col1 = 'a' or col1 = 'b' or col1 = 'c' or col1 = null

The last reduces to null (not false), so you get either TRUE or NULL out
of the OR condition. CHECK constraints are defined to not fail on a null
result (which is not terribly consistent, but it's what the spec says).
So basically that check constraint will never fail.

> Is there a different way I can allow for a static set of values AND null too?

Plain old check (col1 in ('a','b','c')) would work that way. If you
actually want to force it to be non-null, you have to say that
explicitly; usually people use a separate NOT NULL constraint for that.

regards, tom lane