From: Ian Barwick <barwick@gmail.com>
To: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: replacing mysql enum
Date: Sat, 11 Dec 2004 13:25:37 +0100
Message-ID: <1d581afe04121104255557ea@mail.gmail.com> (raw)
In-Reply-To: <04121110305520.21547@thenilgiris.com>
References: <04121110305520.21547@thenilgiris.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: barwick@gmail.com, lawgon@thenilgiris.com
Subject: Re: replacing mysql enum
In-Reply-To: <1d581afe04121104255557ea@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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