Re: Reg: Custom Data Types in PostgreSQL

On Fri, Nov 21, 2008 at 11:18 PM, Raj K <raj.indian.08@gmail.com> wrote:
> Hi all,
>     I am not an expert in DB. So please excuse, if the question is stupid.
>
>     In PostgreSQL, we do support custom data types - say enum type.
>     --> CREATE TYPE testtype AS ENUM {'test1', 'test2'};
>     Now, I was wondering whether we should be using a different table or the
> enum itself for the same?
>     --> CREATE TABLE testtypetable { testtype varchar(3) PRIMARY KEY, id
> serial };
>     A new table would help in any modification of enum values, right?
>     But then there is a mechanism to create this type, so there must be some
> extremely pertinent reason for having the same.
>     Other than ease of use, I couldnt find any.

*) enums have an ordering built in to the type, so if you need to
order a lot of data it's much faster than to join out to get the
ordering criteria.  This is admittedly a fairly narrow use case.
*) If you FK out on a integer key, your tables are full of integer
keys everywhere.  Some people think that's great.  I don't
*) If you FK out on a string key (in this case you might as well make
the enum text the primary key and drop the serial), you lose a little
bit in performance (particularly if the keys are large).
Nevertheless, I use this technique when enums are not appropriate.

merlin