Would "create type" be the solution you are seeking? Ie. Create a
user-defined type with the internal and external representations you desire
and create a table with a column of that type.
Cheers,
Steve
On Thursday 02 January 2003 7:55 am, Peter Depuydt wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html