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.
Let’s try again without that null...
thedb=# create table foo2 (col1 text, constraint chk check (col1 in ('a','b','c')));
CREATE TABLE
thedb=# insert into foo2 (col1) values ('xxx');
ERROR: new row for relation "foo2" violates check constraint "chk"
thedb=#
Getting rid of the “null” fixed it.
Is there a different way I can allow for a static set of values AND null too?
Thanks for any comments !!!