From: Peter Koczan <pjkoczan@gmail.com>
To: Seb <spluque@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: enum data type vs table
Date: Wed, 25 May 2011 17:23:26 -0500
Message-ID: <BANLkTimJGqEx3eRjDQECDHTdbRsxoQcyuQ@mail.gmail.com> (raw)
In-Reply-To: <87ipt8tz5h.fsf@kolob.subpolar.dyndns.org>
References: <87ipt8tz5h.fsf@kolob.subpolar.dyndns.org>
On Tue, May 17, 2011 at 11:23 PM, Seb <spluque@gmail.com> wrote:
> Are there any guidelines for deciding whether to 1) create an enum data
> type or 2) create a table with the set of values and then have foreign
> keys referencing this table? Some fields in a database take a small
> number of values, and I'm not sure which of these routes to take. The
> enum data type seems like a clean way to handle this without creating a
> constellation of tables for all these values, but if one wants to add a
> new label to the enum or make changes to it at some point, then the
> tables using it have to be recreated, so it's quite rigid. Have I got
> this right? Thanks.
I think your choice depends on a few things:
1 - How do you want to interact with the tables? What I mean is, are
you planning on querying, inserting, or updating data to those tables
via text or will you need to join to your reference table? If you
don't want to join, you'll either need to use enum types, use views
(which can be a pain if you want to update a view), or
duplicate/reference the text directly (which is slow and a bad idea
for several reasons).
2 - How much can you tolerate downtime or a busy database? Changing
types is a single transaction and requires an exclusive lock. On small
tables this is negligible, but on big tables it can require downtime.
3 - How often do you really expect changes to the enum type? If adding
a new value to an enum type is truly a rare event, it's . If it's
frequent or regular, you should probably have a table.
I've used both of these approaches and I've found enum types to be
well worth any trouble to drop/recreate types. The changes I've made
have been rare, and I've been able to schedule downtime pretty easily,
so it made the most sense for me.
Also, Postgres 9.1 allows adding values to enum types, so you could
always use that when it is finally released.
Hope this helps,
Cheers,
Peter
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: pjkoczan@gmail.com, spluque@gmail.com
Subject: Re: enum data type vs table
In-Reply-To: <BANLkTimJGqEx3eRjDQECDHTdbRsxoQcyuQ@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