Re: interesting check constraint behavior

"Gauthier, Dave" <dave.gauthier@intel.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
theallowed 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