Skip site navigation (1) Skip section navigation (2)

Question on simulating Enum Data type

From: "Morgan Kita" <mkita(at)verseon(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Question on simulating Enum Data type
Date: 2005-03-18 02:18:33
Message-ID: 08B420FF5BF7BC42A064212C2EB768801C108D@neutron.verseon.com (view raw or flat)
Thread:
Lists: pgsql-novice
Ok this is noobish, but I am new to both databases and especially PostgreSQL.

I am planning out how I am going to setup my database schema, and I have quite a few fields in the different tables where I would like to set up an enum type similar to C and C++ style enum types. Essentially these fields will have like 5-15 choices, and the tables themselves might have on the order of 10 million rows. Now I know I can pretty easily simulate this with domain constraints. 

However, I am a little concerned about performance in that case. If I use domain constraints and keep the choices as strings then a string comparison will be done whenver I query on this field right? I know an index will speed this up quite a bit but even so I may have to do 10s of thousands of string compares if there are only 5 choices right? 

Ideally wouldn't it be better to store an integer field in the tables, and then keep a seperate small map table? Then the application could use the map table to look up the key and then do a query on the large table using only integer compares? 

Am I just being silly or am I not understanding something here? Maybe there is another way to do this?

Thanks,
Morgan

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2005-03-18 02:39:46
Subject: Re: Question on simulating Enum Data type
Previous:From: Tom LaneDate: 2005-03-17 23:33:43
Subject: Re: Multidimensional arrays in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group