Thread: How to release SET() in PgSQL?

Hello All!


I used MySQL for a long time. There is SET() conception.
I can define SET('one','two','three') and use that type:

CREATE TABLE "test" (
    "a" SET('one','two','three')
};

And then:

 SELECT * FROM test WHERE a = 'one';
or
 SELECT * FROM test WHERE a like 'one,three';

How can I do it in PgSQL? I need it so much!


--
with respection Andrey Feofilactovich.
e-mail: feo@ttn.ru, feo@feo.org.ru
ICQ: 28073807

Andrey Y. Mosienko writes:
>  SELECT * FROM test WHERE a like 'one,three';
>
> How can I do it in PgSQL? I need it so much!

It isn't quite as clean, but in the long term I think it's a better
solution:

Use a join to another table with your "SET"/"ENUM" list in it.

Dan

Andrey Y. Mosienko writes:

> CREATE TABLE "test" (
>     "a" SET('one','two','three')
> };

You could split that off into a separate table, e.g.,

CREATE TABLE test1 (
  id int,
  /* the rest of the columns in your "test" */
);

CREATE TABLE test2 (
  id int references test1,
  a varchar check a in ('one', 'two', 'three')
);

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

>> How can I do it in PgSQL? I need it so much!

I think the usual advice is to make it a text or varchar column and
use a check constraint to enforce that only one of the allowed strings
can be stored in it.

            regards, tom lane