Re: Does PostgreSQL support SET or ENUM data types?

Greetings, Maxim!

At 22.06.2001, 09:20, you wrote:
MM> I am not a very great PostgreSQL user, but I know mySQL. In mySQL we have
MM> SET and ENUM.
MM> I now need something like this in PostgreSQL.
    Trust me: you don't. These are ugly crutches to circumvent mySQL's
complete lack of referential integrity. These are unportable, too.

`Emulating` the SET:
------------------------------------------------
create table foo (
       foo_id ...,
       foo1 ...,
       foo2 ...,

       primary key (foo_id)
)

create table foo_set_values (
       set_id ...,
       set_value ...,

       primary key (set_id)
)

create table foo_set (
       foo_id ...,
       set_id ...,

       primary key (foo_id, set_id),
       foreign key (foo_id) references foo,
       foreign key (set_id) references foo_set_values
)
------------------------------------------------

`Emulating` the ENUM (with lookup table)
------------------------------------------------
create table bar_enum (
       enum_id ...,
       enum_value ...,

       primary key (enum_id)
)

create table bar (
       bar_id ...,
       bar1 ...,
       bar2 ...,
       enum_id ...,

       primary key (bar_id),
       foreign key (enum_id) references bar_enum
)
------------------------------------------------

`Emulating` the ENUM (with CHECK)
------------------------------------------------
create table bar (
       bar_id ...,
       bar1 ...,
       bar2 ...,
       bar_enum ... CHECK bar_enum IN ('one', 'two', 'three'),

       primary key (bar_id)
)
------------------------------------------------

     These ARE portable. To anywhere except mySQL.

--
Yours, Alexey V. Borzov, Webmaster of RDW.ru