"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