pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreplacing mysql enum
9+ messages / 7 participants
[nested] [flat]
* replacing mysql enum
@ 2004-12-11 05:00 Kenneth Gonsalves <lawgon@thenilgiris.com>
0 siblings, 2 replies; 9+ messages in thread
From: Kenneth Gonsalves @ 2004-12-11 05:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 12:25 Ian Barwick <barwick@gmail.com>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 2 replies; 9+ messages in thread
From: Ian Barwick @ 2004-12-11 12:25 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
On Sat, 11 Dec 2004 10:30:55 +0530, Kenneth Gonsalves
<lawgon@thenilgiris.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 15:47 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Ian Barwick <barwick@gmail.com>
1 sibling, 2 replies; 9+ messages in thread
From: Stephan Szabo @ 2004-12-11 15:47 UTC (permalink / raw)
To: Ian Barwick <barwick@gmail.com>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; 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'))
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 16:35 Rod Taylor <pg@rbt.ca>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 9+ messages in thread
From: Rod Taylor @ 2004-12-11 16:35 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Ian Barwick <barwick@gmail.com>; Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
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).
--
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 16:45 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2004-12-11 16:45 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Ian Barwick <barwick@gmail.com>; Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
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. :)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 17:06 Ian Barwick <barwick@gmail.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 9+ messages in thread
From: Ian Barwick @ 2004-12-11 17:06 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
On Sat, 11 Dec 2004 07:47:51 -0800 (PST), Stephan Szabo
<sszabo@megazone.bigpanda.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 17:25 Greg Stark <gsstark@mit.edu>
parent: Ian Barwick <barwick@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Greg Stark @ 2004-12-11 17:25 UTC (permalink / raw)
To: Ian Barwick <barwick@gmail.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
Ian Barwick <barwick@gmail.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 17:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ian Barwick <barwick@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2004-12-11 17:56 UTC (permalink / raw)
To: Ian Barwick <barwick@gmail.com>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
Ian Barwick <barwick@gmail.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: replacing mysql enum
@ 2004-12-11 18:16 Josh Berkus <josh@agliodbs.com>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2004-12-11 18:16 UTC (permalink / raw)
To: pgsql-sql; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-12-11 18:16 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-11 05:00 replacing mysql enum Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-11 12:25 ` Ian Barwick <barwick@gmail.com>
2004-12-11 15:47 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-12-11 16:35 ` Rod Taylor <pg@rbt.ca>
2004-12-11 16:45 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-12-11 17:06 ` Ian Barwick <barwick@gmail.com>
2004-12-11 17:25 ` Greg Stark <gsstark@mit.edu>
2004-12-11 17:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-11 18:16 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox