ENUM like data type

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