Skip site navigation (1) Skip section navigation (2)

Re: replacing mysql enum

From: Ian Barwick <barwick(at)gmail(dot)com>
To: Kenneth Gonsalves <lawgon(at)thenilgiris(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: replacing mysql enum
Date: 2004-12-11 12:25:37
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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

(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')));
test=> insert into consttest values ('xx');
INSERT 408080 1
test=> SELECT * from consttest ;
(1 row)

Not sure what logic is driving this).

Ian Barwick

In response to


pgsql-sql by date

Next:From: NosyManDate: 2004-12-11 12:47:09
Previous:From: Joe ConwayDate: 2004-12-11 11:55:44
Subject: Re: Create Calendar

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group