Re: Reg: Custom Data Types in PostgreSQL

On Fri, Nov 21, 2008 at 11:18 PM, Raj K <raj(dot)indian(dot)08(at)gmail(dot)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