Re: replacing mysql enum

hi,

from mysql:

field enum('a','b','c') default null,

i translated this as:

field varchar(2) check (field in (null,'a','b','c')),

is it necessary to put the 'null' in the check condition? if not will pg
permit you to enter a null value in the field?

regards
kg

On Sat, 11 Dec 2004 10:30:55 +0530, Kenneth Gonsalves
<lawgon(at)thenilgiris(dot)com> wrote:
> hi,
>
> from mysql:
>
> field enum('a','b','c') default null,
>
> i translated this as:
>
> field varchar(2) check (field in (null,'a','b','c')),
>
> is it necessary to put the 'null' in the check condition? if not will pg
> permit you to enter a null value in the field?

No, and yes:
create table consttest (field varchar(2) check (field in ('a','b','c')));
insert into consttest values (null);

Note this does not emulate the MySQL ENUM datatype precisely,
because it's possible to reference the ENUM fields by index value
too.

(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).

Ian Barwick

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'))

On Sat, 2004-12-11 at 07:47 -0800, Stephan Szabo wrote:
> 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'))

Actually, he just needs check(field in ('a', 'b', 'c')). NULL is
accepted unless explicitly denied (NOT NULL constraint or an IS NOT NULL
check).

--

On Sat, 11 Dec 2004, Rod Taylor wrote:

> On Sat, 2004-12-11 at 07:47 -0800, Stephan Szabo wrote:
> > 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'))
>
> Actually, he just needs check(field in ('a', 'b', 'c')). NULL is
> accepted unless explicitly denied (NOT NULL constraint or an IS NOT NULL
> check).

Right. For the same reason, even. Really need to stop answering
messages before I wake up. :)

On Sat, 11 Dec 2004 07:47:51 -0800 (PST), Stephan Szabo
<sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> 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'))

This works as expected, although for constraints the nullness
of the column is better off handled by applying NOT NULL if
necessary.

What I still don't quite understand is why IN in a CHECK context is
handled differently to say: select 1 where 'x' in (null,'a','b','c') ?
This could be a bit of a gotcha for anyone constructing a constraint
similar to the original poster's and not realising it has no effect.

Ian Barwick

Ian Barwick <barwick(at)gmail(dot)com> writes:

> What I still don't quite understand is why IN in a CHECK context is
> handled differently to say: select 1 where 'x' in (null,'a','b','c') ?
> This could be a bit of a gotcha for anyone constructing a constraint
> similar to the original poster's and not realising it has no effect.

well

WHERE foo IN (null, ...)

returns null if foo isn't explicitly in the list (ie, "it may or may not equal
the unknown value in the list"). And I think constraints that return null are
deemed to have succeeded.

--
greg

Ian Barwick <barwick(at)gmail(dot)com> writes:
> (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

For a non-null field value, that IN clause reduces to
null OR false OR false OR false
which reduces to null because of the way 3-state boolean logic is
defined in SQL (which makes sense if you interpret null as "unknown").
And a null result from CHECK is defined not to be a failure case by
the SQL standard.

This is really the same logic that allows the explicit-null-free CHECK
condition to accept NULLs:
null IN ('a','b','c')
becomes
null OR null OR null
becomes
null
which doesn't fail.

I believe this was intentional on the part of the SQL committee. Their
thought was that if you intend to disallow NULLs, you should write an
explicit NOT NULL constraint, separately from any CHECK you might write.
Therefore, defining CHECK such that it tend to fall through silently on
NULL inputs is a good thing.

regards, tom lane

Kenneth,

> i translated this as:
>
> field varchar(2) check (field in (null,'a','b','c')),

While this isn't the question you asked, might I encourage you to use DOMAINs
instead? I.e.:

CREATE DOMAIN abc_col AS TEXT
CHECK VALUE IN ( 'a', 'b', 'c' );

Then you declare the table as:

table (
field abc_col,
);

I find that DOMAINs give vastly enhanced managability compared to table
constraints.

--
Josh Berkus
Aglio Database Solutions
San Francisco