"Gauthier, Dave" <dave.gauth...@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 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
