Re: EVAL and SET equivalents in PostgreSQL

This is no doubt asked frequently, but I have exactly two days
experience with PostgreSQL, and am hot on a project for which I need a
more-or-less immediate answer.

Most of my previous DB experience has been with MySQL, which supported
ENUM('value1','value2'...) data types, to allow a column to be
assigned the value of exactly one item from the list, and also the
SET('value1','value2',...) type to allow zero or more members of the
set of values.

I need this functionality or something equivalent in a DB I'm setting
up on a project, and a half day of rumbling around in man pages and
printed documentation has yielded no obvious answer. I did learn about
creating arrays, but that's not exactly what I want.

If I try to INSERT an illegel value for a SET or ENUM field, MySQL
will return an error message, which is what you want it to do.

Without that, you have to add a layer of programming to intercept the
values that are coming in and verify that it's good stuff -- lots of
trouble that I'd like to avoid if possible.

I'm asking this group because I know that surely I am not the first
migrant from MySQL to come up against this problem, and suspect the
answer is common knowledge that I have not yet groked.

Many thanks for any insights shared.

--
Lynn David Newton
Phoenix, AZ

Sure, use CHECK constraints on the column. That is the ANSI standard
way.

gender CHAR(1) CHECK (gender IN ('M','F')),

---------------------------------------------------------------------------

Lynn David Newton wrote:
>
> This is no doubt asked frequently, but I have exactly two days
> experience with PostgreSQL, and am hot on a project for which I need a
> more-or-less immediate answer.
>
> Most of my previous DB experience has been with MySQL, which supported
> ENUM('value1','value2'...) data types, to allow a column to be
> assigned the value of exactly one item from the list, and also the
> SET('value1','value2',...) type to allow zero or more members of the
> set of values.
>
> I need this functionality or something equivalent in a DB I'm setting
> up on a project, and a half day of rumbling around in man pages and
> printed documentation has yielded no obvious answer. I did learn about
> creating arrays, but that's not exactly what I want.
>
> If I try to INSERT an illegel value for a SET or ENUM field, MySQL
> will return an error message, which is what you want it to do.
>
> Without that, you have to add a layer of programming to intercept the
> values that are coming in and verify that it's good stuff -- lots of
> trouble that I'd like to avoid if possible.
>
> I'm asking this group because I know that surely I am not the first
> migrant from MySQL to come up against this problem, and suspect the
> answer is common knowledge that I have not yet groked.
>
> Many thanks for any insights shared.
>
> --
> Lynn David Newton
> Phoenix, AZ
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On Tue, 2 Jul 2002, Lynn David Newton wrote:

> [snip]
>
> Most of my previous DB experience has been with MySQL, which supported
> ENUM('value1','value2'...) data types, to allow a column to be
> assigned the value of exactly one item from the list, and also the
> SET('value1','value2',...) type to allow zero or more members of the
> set of values.
>
> [snip]
>
> If I try to INSERT an illegel value for a SET or ENUM field, MySQL
> will return an error message, which is what you want it to do.

Have you looked at or tried the CHECK constraint? See the SQL code
for CREATE TABLE ...

If you need something more flexible, you can use an auxiliary table
to store the "enum" values, and use a foreign key constraint.

HTH --

-frank

>> If I try to INSERT an illegel value for a SET or
>> ENUM field, MySQL will return an error message,
>> which is what you want it to do.

Frank> Have you looked at or tried the CHECK
Frank> constraint? See the SQL code for CREATE TABLE
Frank> ...

Frank> If you need something more flexible, you can
Frank> use an auxiliary table to store the "enum"
Frank> values, and use a foreign key constraint.

Thank you to everyone who has responded telling me
about the CHECK constraint. I had that figured out late
last night as the solution to substitute for ENUM.

No one who has responded (four people so far) has yet
quite hit the nail on the head regarding a substitute
for MySQL's SET data type. To save myself a lot of
retyping, I'll explain to the list:

Whereas an ENUM may take exactly one of a set of
values, a SET may have zero or more. MySQL displays
them as a comma-separated list. Therefore, if I have a
column abc SET('one','two','three'), acceptable values
of abc are '', 'one', 'two', 'three', 'one,two'
'one,three', 'two,three', and 'one,two,three'.

It's like the difference on a Web form with one field
that uses mutually exclusive radio buttons and another
that uses multiple select buttons. I've used these
often in backend scripts to process MySQL + Perl + CGI
+ DBI + Apache based forms.

(The project at hand has nothing to do with the Web.)

Consider as an example a team where you have a staff of
four members any of whom may be assigned to a given
project. team might equal 'john' or 'joe,judy', or
'john,joe,phil', etc. That's exactly the form I'm used
to seeing these things get passed in from a Web form to
Perl.

This is by no means an obscure thing. In MySQL it's as
simple as can be. Surely there is a straightforward way
to deal with it in PostgreSQL?

Abundant thanks again to those willing to share their
experience.

--
Lynn David Newton
Phoenix, AZ

Bruce Momjian dijo:

> Sure, use CHECK constraints on the column. That is the ANSI standard
> way.
>
> gender CHAR(1) CHECK (gender IN ('M','F')),

That's the solution for the ENUM MySQL datatype, but the SET datatype
allows one to have multiple values in the column (or that's what I
understood in the original message).

In PostgreSQL, you could use an array to store multiple values. I don't
know if one could set up a CHECK constraint so that every element in a
varchar array is checked against a predefined set. Maybe it can be done
at insertion time using a trigger?

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

On Wed, 3 Jul 2002, Alvaro Herrera wrote:

> Bruce Momjian dijo:
>
> > Sure, use CHECK constraints on the column. That is the ANSI standard
> > way.
> >
> > gender CHAR(1) CHECK (gender IN ('M','F')),
>
> That's the solution for the ENUM MySQL datatype, but the SET datatype
> allows one to have multiple values in the column (or that's what I
> understood in the original message).
>
> In PostgreSQL, you could use an array to store multiple values. I don't
> know if one could set up a CHECK constraint so that every element in a
> varchar array is checked against a predefined set. Maybe it can be done
> at insertion time using a trigger?

Should work find if you've got a function that does the check. Using
contrib/array you could do the ugly check (col *='M' or col *='F')
which at least seems to work on current sources. You could do something
with regexs or build another function that checks a list of values.
However, that doesn't prevent you from having duplicates (I don't know how
MySQL's set works).

Or a details table and options table with the appropriate foreign keys.

> This is by no means an obscure thing. In MySQL it's as
> simple as can be. Surely there is a straightforward way
> to deal with it in PostgreSQL?

Yes. Use normalized relations, where SQL is specifically designed to
deal with sets, to enforce uniqueness, etc etc. As you know, that
technique is portable to systems other than PostgreSQL too...

- Thomas

On Wed, 3 Jul 2002, Lynn David Newton wrote:

> No one who has responded (four people so far) has yet
> quite hit the nail on the head regarding a substitute
> for MySQL's SET data type.

Well, as someone else pointed out, there's always the option of
doing it the relational way, and just creating a separate table
for this.

However, if you want to do what MySQL does internally, and do it
in a more portable way, you can just use bitfields in an int or
decimal of appropriate size. Just assign numbers to your enum values
(0 = foo, 1 = bar, 2 = bam., etc.) and use bit operations to set
and unset them.

-- Set foo:
UPDATE mytable SET flags = flags & (1 << 0) WHERE ...
-- Set bam:
UPDATE mytable SET flags = flags & (1 << 2) WHERE ...
-- Unset bar:
UPDATE mytable SET flags = flags | ~(1 << 1) WHERE ...

You may find it convenient to create functions to deal with this,
so you can just pass in 'foo' or 'bam' rather than having to convert
to the number.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

I just noticed this in Freshmeat - it may be helpful.

"Perl utility to convert MySQL dumps into Postgres dump.
(MySQL and Postgres are the most popular and advanced
open-source database engines.) Emulates ENUMs and
SETs via user types and operators."

"Since version 7.1beta5, my2pg is a part of Postgres
distribution and can be found in the collection of
contributed code under contrib/mysql directory."

http://ziet.zhitomir.ua./~fonin/code/

cjs(at)cynic(dot)net (Curt Sampson) wrote in message news:<Pine(dot)NEB(dot)4(dot)44(dot)0207050915111(dot)22637-100000(at)angelic(dot)cynic(dot)net>...
> On Wed, 3 Jul 2002, Lynn David Newton wrote:
>
> > No one who has responded (four people so far) has yet
> > quite hit the nail on the head regarding a substitute
> > for MySQL's SET data type.
>
> Well, as someone else pointed out, there's always the option of
> doing it the relational way, and just creating a separate table
> for this.
>
> However, if you want to do what MySQL does internally, and do it
> in a more portable way, you can just use bitfields in an int or
...