From: Dawid Kuroczko <qnex42@gmail.com>
To: Martín Marqués <martin@bugs.unl.edu.ar>
Cc: PFC <lists@boutiquenumerique.com>
Cc: Mike Rylander <mrylander@gmail.com>
Cc: pgsql-sql@postgresql.org, PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: ENUM like data type
Date: Wed, 29 Jun 2005 09:09:27 +0200
Message-ID: <758d5e7f05062900097ed5151c@mail.gmail.com> (raw)
In-Reply-To: <200506281522.25124.martin@bugs.unl.edu.ar>
References: <11bgc1oblfasq3d@corp.supernews.com>
<b918cf3d050628042253044ba@mail.gmail.com>
<op.ss3aruu5th1vuj@localhost>
<200506281522.25124.martin@bugs.unl.edu.ar>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: qnex42@gmail.com, martin@bugs.unl.edu.ar, lists@boutiquenumerique.com, mrylander@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: ENUM like data type
In-Reply-To: <758d5e7f05062900097ed5151c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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