Thread: ENUM like data type
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
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 ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
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
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.
>> 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.
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 ---------------------------------------------------------
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.
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
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
| |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 |
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 ---------------------------------------------------------
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. --
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/
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
|> > 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 |
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/