pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedENUM like data type
9+ messages / 9 participants
[nested] [flat]
* ENUM like data type
@ 2005-06-21 15:20 MRB <nomail@example.com>
0 siblings, 3 replies; 9+ messages in thread
From: MRB @ 2005-06-21 15:20 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-28 06:41 Kenneth Gonsalves <lawgon@thenilgiris.com>
parent: MRB <nomail@example.com>
2 siblings, 0 replies; 9+ messages in thread
From: Kenneth Gonsalves @ 2005-06-28 06:41 UTC (permalink / raw)
To: MRB <nomail@example.com>; +Cc: pgsql-sql@postgresql.org
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
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-28 11:22 Mike Rylander <mrylander@gmail.com>
parent: MRB <nomail@example.com>
2 siblings, 1 reply; 9+ messages in thread
From: Mike Rylander @ 2005-06-28 11:22 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-28 12:37 Bruno Wolff III <bruno@wolff.to>
parent: MRB <nomail@example.com>
2 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2005-06-28 12:37 UTC (permalink / raw)
To: MRB <nomail@example.com>; +Cc: pgsql-sql@postgresql.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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-28 16:58 PFC <lists@boutiquenumerique.com>
parent: Mike Rylander <mrylander@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: PFC @ 2005-06-28 16:58 UTC (permalink / raw)
To: Mike Rylander <mrylander@gmail.com>; pgsql-sql@postgresql.org
>> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-28 18:22 Martín Marqués <martin@bugs.unl.edu.ar>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 3 replies; 9+ messages in thread
From: Martín Marqués @ 2005-06-28 18:22 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: Mike Rylander <mrylander@gmail.com>; pgsql-sql@postgresql.org
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
---------------------------------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-28 18:46 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Martín Marqués <martin@bugs.unl.edu.ar>
2 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2005-06-28 18:46 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: PFC <lists@boutiquenumerique.com>; Mike Rylander <mrylander@gmail.com>; pgsql-sql@postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-28 19:55 Nick Johnson <arachnid@notdot.net>
parent: Martín Marqués <martin@bugs.unl.edu.ar>
2 siblings, 0 replies; 9+ messages in thread
From: Nick Johnson @ 2005-06-28 19:55 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ENUM like data type
@ 2005-06-29 07:09 Dawid Kuroczko <qnex42@gmail.com>
parent: Martín Marqués <martin@bugs.unl.edu.ar>
2 siblings, 0 replies; 9+ messages in thread
From: Dawid Kuroczko @ 2005-06-29 07:09 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: PFC <lists@boutiquenumerique.com>; Mike Rylander <mrylander@gmail.com>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-06-29 07:09 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-21 15:20 ENUM like data type MRB <nomail@example.com>
2005-06-28 06:41 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2005-06-28 11:22 ` Mike Rylander <mrylander@gmail.com>
2005-06-28 16:58 ` PFC <lists@boutiquenumerique.com>
2005-06-28 18:22 ` Martín Marqués <martin@bugs.unl.edu.ar>
2005-06-28 18:46 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-06-28 19:55 ` Nick Johnson <arachnid@notdot.net>
2005-06-29 07:09 ` Dawid Kuroczko <qnex42@gmail.com>
2005-06-28 12:37 ` Bruno Wolff III <bruno@wolff.to>
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