Thread: ENUM like data type

ENUM like data type

From
MRB
Date:
Hi All,

I have something in mind I'm not certain is do-able.

I'm working with a lot of data from MySQL where the MySQL ENUM type is used.

This is not a big problem per se but creating the proper lookup tables 
is becoming a bit tedious so I was hoping to make something better of it.

Here is where I get uncertain as to if this is possible. My idea is to 
create a pseudo type that triggers the creation of it's lookup tables 
the same way the SERIAL type triggers creation of a sequence and returns 
an int with the right default value.

Here is what would want to happen:

CREATE TABLE test(   testfield ENUM('Bits', 'of', 'data')
);

-- This would trigger the following events:

CREATE TABLE test_testfield_enum(    id VARCHAR(4) NOT NULL PRIMARY KEY;
);
INSERT INTO test_testfield_enum VALUES('Bits');
INSERT INTO test_testfield_enum VALUES('Of');
INSERT INTO test_testfield_enum VALUES('data');

CREATE TABLE test(    testfield VARCHAR(4) REFERENCES test_testfield_enum(id)
);


Hope that is clear enough. As said I am not quite sure this is possible, 
my guess would be no. But any input is appreciated.

Cheers,

Martin


Re: ENUM like data type

From
Kenneth Gonsalves
Date:
On Tuesday 21 Jun 2005 8:50 pm, MRB wrote:
> I'm working with a lot of data from MySQL where the MySQL ENUM type
> is used.

just a thought - it took me five years after migrating from mysql to
pg to start thinking like an sql programmer. I used to keep trying to
write stuff for pg 'like' i used to for mysql. When i did that - i
didnt get any of the benefits of pg - might as well have continued to
write in mysql. Especially so when i went into convolutions trying to
translate the enum type and the 'set' syntax on insert. In these
cases i suggest dumping the mysql way and rewriting in the pg way.

--
regards
kg

http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!


Re: ENUM like data type

From
Mike Rylander
Date:
On 6/21/05, MRB <nomail@example.com> wrote:
> Hi All,
>
> I have something in mind I'm not certain is do-able.
>
> I'm working with a lot of data from MySQL where the MySQL ENUM type is used.
>

MySQL's ENUM is basically a wrapper for CHECK.  You can use a CHECK
constraint like this:

CREATE TABLE test(   testfield TEXT CHECK (testfield IN( 'Bits', 'of', 'data'))
);

> This is not a big problem per se but creating the proper lookup tables
> is becoming a bit tedious so I was hoping to make something better of it.
>
> Here is where I get uncertain as to if this is possible. My idea is to
> create a pseudo type that triggers the creation of it's lookup tables
> the same way the SERIAL type triggers creation of a sequence and returns
> an int with the right default value.

Although you can't create a generic type to handle this, you can
create a DOMAIN to wrap up your constraint for each "enum" type field
that you want:

CREATE DOMAIN fruit AS TEXT CHECK (VALUE IN ('apple','orange','banana'));
CREATE TABLE eat ( food fruit
);

http://www.postgresql.org/docs/8.0/interactive/sql-createdomain.html

Hope that helps.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org


Re: ENUM like data type

From
Bruno Wolff III
Date:
On Tue, Jun 21, 2005 at 17:20:19 +0200, MRB <nomail@example.com> wrote:
> 
> Here is where I get uncertain as to if this is possible. My idea is to 
> create a pseudo type that triggers the creation of it's lookup tables 
> the same way the SERIAL type triggers creation of a sequence and returns 
> an int with the right default value.

You might want to look at using a domain to do this.


Re: ENUM like data type

From
PFC
Date:
>> Here is where I get uncertain as to if this is possible. My idea is to
>> create a pseudo type that triggers the creation of it's lookup tables
>> the same way the SERIAL type triggers creation of a sequence and returns
>> an int with the right default value.
Personnally I use one table which has columns (domain, name) and which  
stores all enum values for all different enums.I have then CHECK( is_in_domain( column, 'domain_name' )) which is a  
simple function which checks existence of the value in this domain (SELECT  
1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance.You can also use integers.


Re: ENUM like data type

From
Martín Marqués
Date:
El Mar 28 Jun 2005 13:58, PFC escribió:
>
> >> Here is where I get uncertain as to if this is possible. My idea is to
> >> create a pseudo type that triggers the creation of it's lookup tables
> >> the same way the SERIAL type triggers creation of a sequence and returns
> >> an int with the right default value.
>
>     Personnally I use one table which has columns (domain, name) and which
> stores all enum values for all different enums.
>     I have then CHECK( is_in_domain( column, 'domain_name' )) which is a
> simple function which checks existence of the value in this domain (SELECT
> 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance.
>     You can also use integers.

I personally think that the ENUM data type is for databases that are not well
designed. So, if you see the need for ENUM, that means you need to re-think
your data design.

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
---------------------------------------------------------
Martín Marqués          |   Programador, DBA
Centro de Telemática    |     Administrador              Universidad Nacional                   del Litoral
---------------------------------------------------------


Re: ENUM like data type

From
Scott Marlowe
Date:
On Tue, 2005-06-28 at 13:22, Martín Marqués wrote:
> El Mar 28 Jun 2005 13:58, PFC escribió:
> >
> > >> Here is where I get uncertain as to if this is possible. My idea is to
> > >> create a pseudo type that triggers the creation of it's lookup tables
> > >> the same way the SERIAL type triggers creation of a sequence and returns
> > >> an int with the right default value.
> >
> >     Personnally I use one table which has columns (domain, name) and which
> > stores all enum values for all different enums.
> >     I have then CHECK( is_in_domain( column, 'domain_name' )) which is a
> > simple function which checks existence of the value in this domain (SELECT
> > 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance.
> >     You can also use integers.
>
> I personally think that the ENUM data type is for databases that are not well
> designed. So, if you see the need for ENUM, that means you need to re-think
> your data design.

I would generally agree, however, a pseudo type like serial that created
the properly fk'd table with all the options in it would be quite nice
to have.



Re: ENUM like data type

From
Nick Johnson
Date:
Martín Marqués wrote:

>
>I personally think that the ENUM data type is for databases that are not well 
>designed. So, if you see the need for ENUM, that means you need to re-think 
>your data design.
>  
>
You mean like all those instances in the PostgreSQL system catalogs 
where character(1) has been used as a pseudo-enum of sorts?

-Nick Johnson


Re: ENUM like data type

From
Dawid Kuroczko
Date:
On 6/28/05, Martín Marqués <martin@bugs.unl.edu.ar> wrote:
> El Mar 28 Jun 2005 13:58, PFC escribió:
> >       Personnally I use one table which has columns (domain, name) and which
> > stores all enum values for all different enums.
> >       I have then CHECK( is_in_domain( column, 'domain_name' )) which is a
> > simple function which checks existence of the value in this domain (SELECT
> > 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance.
> >       You can also use integers.
>
> I personally think that the ENUM data type is for databases that are not well
> designed. So, if you see the need for ENUM, that means you need to re-think
> your data design.

I seem to remember some discussion here, half a year ago perhaps
which was about something similar (while not exactly).  I mean it

I think it someone said that DB2 (I am not sure about that one)
has a feature that enables it to normalize the table behind the
scenes.

As I remember it, it works somewhere along the lines of:
-- you create table
CREATE TABLE foo (   when timestamptz,   useragent some_data_type_perhaps
);

...and RDBMS will create a lookup table for useragents for you,
with serial key, etc, etc.  And in our foo table useragent will be
kept as a reference to that lookup table.  When you do a select,
lookup table will be consulted behind the scenes, etc, etc.

All this is doable with RULEs and VIEWs (and triggers for populating).

Well, what MRB had in mind was more like a special subcase
of such approach (lookup table with "read-only" keys), but I think
such a lookup table would be benefitial for many users, especially
when dealing with large tables.

Incidentally, does it qualify for todo?  Or maybe its already there?
 Regards,    Dawid


Re: ENUM like data type

From
KÖPFERL Robert
Date:
|
|I personally think that the ENUM data type is for databases
|that are not well
|designed. So, if you see the need for ENUM, that means you
|need to re-think
|your data design.
|

I disagree. In several relations (views of the world) one needs to have a
hand full of well defined values while
integers or bools are not appropriate and strings are too free form.
For example male female or true and false. Whilst the second has a well
known type, other things like a telephone number type don't have such type.
So a new one should be created instead of (varchar/ xy between 4 and 8). One
might argue that new 1:n relations with integrity shall be established but
this is in my eyes overkill for a constant number of values.
Also think of schema export without data. These values are part of the
schema data but not the schema itself which in this sense is wrong


|--
|select 'mmarques' || '@' || 'unl.edu.ar' AS email;
|---------------------------------------------------------
|Martín Marqués          |   Programador, DBA
|Centro de Telemática    |     Administrador
|               Universidad Nacional
|                    del Litoral
|---------------------------------------------------------
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 4: Don't 'kill -9' the postmaster
|


Re: ENUM like data type

From
Martín Marqués
Date:
El Mié 29 Jun 2005 09:40, KÖPFERL Robert escribió:
>
> |
> |I personally think that the ENUM data type is for databases
> |that are not well
> |designed. So, if you see the need for ENUM, that means you
> |need to re-think
> |your data design.
> |
>
> I disagree. In several relations (views of the world) one needs to have a
> hand full of well defined values while
> integers or bools are not appropriate and strings are too free form.
> For example male female or true and false. Whilst the second has a well
> known type, other things like a telephone number type don't have such type.
> So a new one should be created instead of (varchar/ xy between 4 and 8). One
> might argue that new 1:n relations with integrity shall be established but
> this is in my eyes overkill for a constant number of values.
> Also think of schema export without data. These values are part of the
> schema data but not the schema itself which in this sense is wrong

Please, most of this can be done with CHECK(...).

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
---------------------------------------------------------
Martín Marqués          |   Programador, DBA
Centro de Telemática    |     Administrador              Universidad Nacional                   del Litoral
---------------------------------------------------------


Re: ENUM like data type

From
Rod Taylor
Date:
On Wed, 2005-06-29 at 10:21 -0300, Martín Marqués wrote:
> El Mié 29 Jun 2005 09:40, KÖPFERL Robert escribió:
> > 
> > |
> > |I personally think that the ENUM data type is for databases 
> > |that are not well 
> > |designed. So, if you see the need for ENUM, that means you 
> > |need to re-think 
> > |your data design.
> > |
> > 
> > I disagree. In several relations (views of the world) one needs to have a
> > hand full of well defined values while
> > integers or bools are not appropriate and strings are too free form.
> > For example male female or true and false. Whilst the second has a well
> > known type, other things like a telephone number type don't have such type.
> > So a new one should be created instead of (varchar/ xy between 4 and 8). One
> > might argue that new 1:n relations with integrity shall be established but
> > this is in my eyes overkill for a constant number of values.
> > Also think of schema export without data. These values are part of the
> > schema data but not the schema itself which in this sense is wrong
> 
> Please, most of this can be done with CHECK(...).

Indeed. A CHECK constraint on a DOMAIN is an ENUM plus some.

-- 



Re: ENUM like data type

From
Peter Eisentraut
Date:
Rod Taylor wrote:
> Indeed. A CHECK constraint on a DOMAIN is an ENUM plus some.

Not really.  A domain doesn't create a new type.  If you base your enum 
domains on the text type, as would usually be the case, then nothing 
stops you from using, say, text concatenation operators and the like.  
I suppose in practice this won't matter too much, but it can't be 
called a clean design.  What you'd really need is a way to create a 
distinct type.  SQL has a feature for that, but PostgreSQL hasn't 
implemented it.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: ENUM like data type

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Rod Taylor wrote:
>> Indeed. A CHECK constraint on a DOMAIN is an ENUM plus some.

> Not really.  A domain doesn't create a new type.  If you base your enum 
> domains on the text type, as would usually be the case, then nothing 
> stops you from using, say, text concatenation operators and the like.  
> I suppose in practice this won't matter too much, but it can't be 
> called a clean design.  What you'd really need is a way to create a 
> distinct type.  SQL has a feature for that, but PostgreSQL hasn't 
> implemented it.

It's not that hard to make your own type using the builtin textin and
textout functions, and then add just the functions you wish to provide.
        regards, tom lane


Re: ENUM like data type

From
KÖPFERL Robert
Date:
|> > I disagree. In several relations (views of the world) one 
|needs to have a
|> > hand full of well defined values while
|> > integers or bools are not appropriate and strings are too 
|free form.
|> > For example male female or true and false. Whilst the 
|second has a well
|> > known type, other things like a telephone number type 
|don't have such type.
|> > So a new one should be created instead of (varchar/ xy 
|between 4 and 8). One
|> > might argue that new 1:n relations with integrity shall be 
|established but
|> > this is in my eyes overkill for a constant number of values.
|> > Also think of schema export without data. These values are 
|part of the
|> > schema data but not the schema itself which in this sense is wrong
|> 
|> Please, most of this can be done with CHECK(...).
|
|Indeed. A CHECK constraint on a DOMAIN is an ENUM plus some.

Yea, that's what also I currently doing. But using varchars instead of
symbolic values (as int4) is just more efficient.


|
|-- 
|
|
|---------------------------(end of 
|broadcast)---------------------------
|TIP 5: Have you checked our extensive FAQ?
|
|               http://www.postgresql.org/docs/faq
|


Re: ENUM like data type

From
Peter Eisentraut
Date:
Am Donnerstag, 30. Juni 2005 00:55 schrieb Tom Lane:
> It's not that hard to make your own type using the builtin textin and
> textout functions, and then add just the functions you wish to provide.

Implementing the "distinct type" feature of SQL would probably amount to 
something like that.  Might be worth looking into.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/