pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedENUM like data type
16+ messages / 13 participants
[nested] [flat]
* ENUM like data type
@ 2005-06-21 15:20 MRB <nomail@example.com>
0 siblings, 3 replies; 16+ messages in thread
From: MRB @ 2005-06-21 15:20 UTC (permalink / raw)
To: pgsql-sql
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] 16+ 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; 16+ messages in thread
From: Kenneth Gonsalves @ 2005-06-28 06:41 UTC (permalink / raw)
To: MRB <nomail@example.com>; +Cc: pgsql-sql
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] 16+ 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; 16+ messages in thread
From: Mike Rylander @ 2005-06-28 11:22 UTC (permalink / raw)
To: pgsql-sql
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] 16+ 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; 16+ messages in thread
From: Bruno Wolff III @ 2005-06-28 12:37 UTC (permalink / raw)
To: MRB <nomail@example.com>; +Cc: pgsql-sql
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] 16+ 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; 16+ messages in thread
From: PFC @ 2005-06-28 16:58 UTC (permalink / raw)
To: Mike Rylander <mrylander@gmail.com>; pgsql-sql
>> 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] 16+ 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; 16+ 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
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] 16+ 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; 16+ 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
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] 16+ 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; 16+ messages in thread
From: Nick Johnson @ 2005-06-28 19:55 UTC (permalink / raw)
To: pgsql-sql
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] 16+ 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; 16+ 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-sql@postgresql.org, PostgreSQL-development <pgsql-hackers@postgresql.org>
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] 16+ messages in thread
* Re: ENUM like data type
@ 2005-06-29 12:40 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 1 reply; 16+ messages in thread
From: KÖPFERL Robert @ 2005-06-29 12:40 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
|
|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
|
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ENUM like data type
@ 2005-06-29 13:21 Martín Marqués <martin@bugs.unl.edu.ar>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 1 reply; 16+ messages in thread
From: Martín Marqués @ 2005-06-29 13:21 UTC (permalink / raw)
To: pgsql-sql
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
---------------------------------------------------------
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ENUM like data type
@ 2005-06-29 13:28 Rod Taylor <pg@rbt.ca>
parent: Martín Marqués <martin@bugs.unl.edu.ar>
0 siblings, 1 reply; 16+ messages in thread
From: Rod Taylor @ 2005-06-29 13:28 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
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.
--
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ENUM like data type
@ 2005-06-29 21:20 Peter Eisentraut <peter_e@gmx.net>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 16+ messages in thread
From: Peter Eisentraut @ 2005-06-29 21:20 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Martín Marqués <martin@bugs.unl.edu.ar>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ENUM like data type
@ 2005-06-29 22:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2005-06-29 22:55 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Rod Taylor <pg@rbt.ca>; Martín Marqués <martin@bugs.unl.edu.ar>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ENUM like data type
@ 2005-06-30 08:01 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 16+ messages in thread
From: KÖPFERL Robert @ 2005-06-30 08:01 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
|> > 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
|
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ENUM like data type
@ 2005-06-30 11:13 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Peter Eisentraut @ 2005-06-30 11:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rod Taylor <pg@rbt.ca>; Martín Marqués <martin@bugs.unl.edu.ar>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2005-06-30 11:13 UTC | newest]
Thread overview: 16+ 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>
2005-06-29 12:40 Re: ENUM like data type KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-29 13:21 ` Martín Marqués <martin@bugs.unl.edu.ar>
2005-06-29 13:28 ` Rod Taylor <pg@rbt.ca>
2005-06-29 21:20 ` Peter Eisentraut <peter_e@gmx.net>
2005-06-29 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-30 11:13 ` Peter Eisentraut <peter_e@gmx.net>
2005-06-30 08:01 Re: ENUM like data type KÖPFERL Robert <robert.koepferl@sonorys.at>
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