Thread: interesting check constraint behavior
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 !!!
"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
On 7 Jan 2010, at 24:12, Gauthier, Dave wrote:
> 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.
As Tom already explained, "value in ('yada', 'yada', null)" evaluates to null. You'll need to make sure your check
expressionevaluates to either true or false - not null.
I guess you need: check(col1 in ('a', 'b', 'c') or col1 is null)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4b45bc2610731598743439!
Both suggestions (exclude the 'null' from the list, and include "or col1 is null") work.
And if I ever wanted to require that col1 is not null, just add that as a separate constraint.
Thanks guys for the advise and explanation !
-----Original Message-----
From: Alban Hertroys [mailto:dalroi@solfertje.student.utwente.nl]
Sent: Thursday, January 07, 2010 5:49 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] interesting check constraint behavior
On 7 Jan 2010, at 24:12, Gauthier, Dave wrote:
> 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.
As Tom already explained, "value in ('yada', 'yada', null)" evaluates to null. You'll need to make sure your check
expressionevaluates to either true or false - not null.
I guess you need: check(col1 in ('a', 'b', 'c') or col1 is null)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:1015,4b45bc2510731992717809!