Datatype SET or enumeration type ?

From: "Peter Depuydt" <peter(at)dynetix(dot)be>
To: pgsql-general(at)postgresql(dot)org
Subject: Datatype SET or enumeration type ?
Date: 2003-01-02 15:55:02
Message-ID: av1nq2$24qq$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello smart people,

I've been mining the web for an explication but alas I haven't found one.
So here is my the problem I want to solve in a nice way.

In a table I need a datatype that contains one of x possibilities, where x
is fixed from the beginning.
I could use an 'int2' type for instance, but that makes the desing unclear,
otherwise I could make
a separate table wich contains the real-world translations but that seems
way over kill.

Here is an example of what I actualy mean:

******************
The int2 solution :

create table order (
"order_id" serial primary key,
"order_status" int2 not null /* where 1=on hold, 2= on order,3=in
backorder,4=instock,5=sold */
ETC ...
); /* end create table order

******************
The extra table solution :

create table order_status (
"order_status_id" int2 unique not null
"order_status_descr" varchar(20)
); /* end create table order_status */

create table order (
"order_id" serial primary key,
"order_status_id" int2 not null references order_status on update no
action,
ETC ...
); /* end create table order */

Life would be easier and the database design nicer if there where a solution
like :

create set "order_status_set" with {"on hold","on order","in
backorder","in stock","sold"}

where the internal representation could be an integer ...

so that I could use it as follows :

create table order (
"order_id" serial not null primary key,
"order_status" order_status_set not null default("on hold"),
ETC ...
); /* end create table order */

The way I see it, the datatype SET ( a set of tuples) would be the way to do
it, however I cannot find
any detailed information about it. On Postgresql 7.2.3 I cann't even create
a column with type SET.

Any suggestions, solutions or explanations ?

Thanks and all the best for the new year ;-)

Peter Depuydt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Warren Massengill 2003-01-02 16:01:02 Re: Cast your vote ...
Previous Message scott.marlowe 2003-01-02 15:52:10 Re: compiling 7.3 on RH7.2