From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Ian Barwick <barwick(at)gmail(dot)com> |
Cc: | Kenneth Gonsalves <lawgon(at)thenilgiris(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: replacing mysql enum |
Date: | 2004-12-11 15:47:51 |
Message-ID: | 20041211074227.J30051@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sat, 11 Dec 2004, Ian Barwick wrote:
> (Oddly enough, putting the NULL in the CHECK constraint seems
> to make the constraint worthless:
> test=> create table consttest (field varchar(2) check (field in
> (null, 'a','b','c')));
> CREATE TABLE
> test=> insert into consttest values ('xx');
> INSERT 408080 1
> test=> SELECT * from consttest ;
> field
> -------
> xx
> (1 row)
>
> Not sure what logic is driving this).
The way NULL is handled in IN (because it's effectively an equality
comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
never return false and constraints are satisified unless the search
condition returns false for some row. I think this means you need the
more verbose (field is null or field in ('a','b','c'))
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2004-12-11 16:25:39 | Re: [GENERAL] Query is not using index when it should |
Previous Message | Steinar H. Gunderson | 2004-12-11 14:32:13 | Re: [GENERAL] Query is not using index when it should |