If people would like to play, I have created a little kit to help in
creating first class enum types in a few seconds. It works something
like this:

make TYPENAME=rainbow ENUMS=' "red", "orange", "yellow", "green",
"blue", "indigo", "violet" '
make TYPENAME=rainbow install
psql -f /path/to/contrib/rainbow-install.sql yourdb

and you are done. Now you can do:

create table foo( r rainbow);
insert into foo values('red');
select 'red'::rainbow < 'green'::rainbow; <-- yields true
select rainbow_order('yellow'); <-- yields 2

The kit is at http://developer.postgresql.org/~adunstan/enumkit.tgz

Needs 8.0 or later, since it use PGXS.

Maximum number of values is 32767 - but if you use that many you're
insane anyway :-)

I did this as part of thinking about how we might do enums properly. AS
Chris KL recently noted - it is very often asked for. So this is not the
end of the road, just a tiny step at the beginning.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

This is cool; it's something people can use today if nothing else.
Long-term, is it practical to have the enums compiled in? ISTM that's
not very workable, but I'm completely guessing. The other issue is that
this version makes it very difficult to change what's in the enum (not
that that's at all easy with MySQL...)

On Thu, Oct 27, 2005 at 03:47:53PM -0400, Andrew Dunstan wrote:
>
> If people would like to play, I have created a little kit to help in
> creating first class enum types in a few seconds. It works something
> like this:
>
> make TYPENAME=rainbow ENUMS=' "red", "orange", "yellow", "green",
> "blue", "indigo", "violet" '
> make TYPENAME=rainbow install
> psql -f /path/to/contrib/rainbow-install.sql yourdb
>
> and you are done. Now you can do:
>
> create table foo( r rainbow);
> insert into foo values('red');
> select 'red'::rainbow < 'green'::rainbow; <-- yields true
> select rainbow_order('yellow'); <-- yields 2
>
> The kit is at http://developer.postgresql.org/~adunstan/enumkit.tgz
>
> Needs 8.0 or later, since it use PGXS.
>
> Maximum number of values is 32767 - but if you use that many you're
> insane anyway :-)
>
> I did this as part of thinking about how we might do enums properly. AS
> Chris KL recently noted - it is very often asked for. So this is not the
> end of the road, just a tiny step at the beginning.
>
> cheers
>
> andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

This little snippet is great! The only problem I see is that the enums must
be consistent across all modules.

What about loading a variable with a "default" value? Then it could be
adjusted to 'play'.

On 10/27/05, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> This is cool; it's something people can use today if nothing else.
> Long-term, is it practical to have the enums compiled in? ISTM that's
> not very workable, but I'm completely guessing. The other issue is that
> this version makes it very difficult to change what's in the enum (not
> that that's at all easy with MySQL...)
>
> On Thu, Oct 27, 2005 at 03:47:53PM -0400, Andrew Dunstan wrote:
> >
> > If people would like to play, I have created a little kit to help in
> > creating first class enum types in a few seconds. It works something
> > like this:
> >
> > make TYPENAME=rainbow ENUMS=' "red", "orange", "yellow", "green",
> > "blue", "indigo", "violet" '
> > make TYPENAME=rainbow install
> > psql -f /path/to/contrib/rainbow-install.sql yourdb
> >
> > and you are done. Now you can do:
> >
> > create table foo( r rainbow);
> > insert into foo values('red');
> > select 'red'::rainbow < 'green'::rainbow; <-- yields true
> > select rainbow_order('yellow'); <-- yields 2
> >
> > The kit is at http://developer.postgresql.org/~adunstan/enumkit.tgz
> >
> > Needs 8.0 or later, since it use PGXS.
> >
> > Maximum number of values is 32767 - but if you use that many you're
> > insane anyway :-)
> >
> > I did this as part of thinking about how we might do enums properly. AS
> > Chris KL recently noted - it is very often asked for. So this is not the
> > end of the road, just a tiny step at the beginning.
> >
> > cheers
> >
> > andrew
>
>

On Thu, Oct 27, 2005 at 04:54:36PM -0400, Ted Rolle wrote:
> This little snippet is great! The only problem I see is that the enums must
> be consistent across all modules.
>
> What about loading a variable with a "default" value? Then it could be
> adjusted to 'play'.

Huh? Sorry, but you completely lost me here...

On another note, I noticed that the comparison operators seem to be
comparing the underlying numeric value used to store the enum, which is
wrong IMO. Consider:

ENUM color '"red","blue","green"'
CREATE TABLE t (c color);
INSERT INTO t VALUES('blue');
INSERT INTO t VALUES('green');
INSERT INTO t VALUES('red');
SELECT c FROM t ORDER BY c;
red
blue
green

That seems counter-intuitive. It's also exposing an implimentation
detail (that the enum is stored internally as a number).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby wrote:

>
>On another note, I noticed that the comparison operators seem to be
>comparing the underlying numeric value used to store the enum, which is
>wrong IMO. Consider:
>
>ENUM color '"red","blue","green"'
>CREATE TABLE t (c color);
>INSERT INTO t VALUES('blue');
>INSERT INTO t VALUES('green');
>INSERT INTO t VALUES('red');
>SELECT c FROM t ORDER BY c;
>red
>blue
>green
>
>That seems counter-intuitive. It's also exposing an implimentation
>detail (that the enum is stored internally as a number).
>
>

No it is not. Not in the slightest. It is honoring the enumeration order
defined for the type. That is the ONLY correct behaviour, IMNSHO.
Otherwise, you could just as easily use a domain with a check constraint.

In fact, mysql's behaviour is laughably, even ludicrously, inconsistent:

mysql> select color from t order by color;
+-------+
| color |
+-------+
| red |
| blue |
| green |
+-------+
3 rows in set (0.06 sec)

mysql> select * from t where color < 'green';
+-------+
| color |
+-------+
| blue |
+-------+

So for "order by" it honors the enumeration order, but for < it uses the
lexical ordering. Lovely, eh?

cheers

andrew


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, Oct 27, 2005 at 07:02:45PM -0400, Andrew Dunstan wrote:
> Jim C. Nasby wrote:
> >On another note, I noticed that the comparison operators seem to be
> >comparing the underlying numeric value used to store the enum, which is
> >wrong IMO. Consider:
> >
> >ENUM color '"red","blue","green"'
> >CREATE TABLE t (c color);
> >INSERT INTO t VALUES('blue');
> >INSERT INTO t VALUES('green');
> >INSERT INTO t VALUES('red');
> >SELECT c FROM t ORDER BY c;
> >red
> >blue
> >green
> >
> >That seems counter-intuitive. It's also exposing an implimentation
> >detail (that the enum is stored internally as a number).
>
> No it is not. Not in the slightest. It is honoring the enumeration order
> defined for the type. That is the ONLY correct behaviour, IMNSHO.

I agree. Honoring the enumeration order makes sense if you consider
the values as things that should be ordered based on some property
of their thingness instead of based on what their labels happen to
be in a particular language. If I have an enumeration of colors I
might want values sorted by their position in the spectrum, so
whether the labels are (red, green, blue) or (gorri, berde, urdin)
I might want to maintain that particular order.

If you want values ordered lexically then you can enumerate them
that way. Why force that behavior on people who want to order based
on some other criteria?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, Oct 27, 2005 at 05:41:01PM -0600, Michael Fuhr wrote:
> If you want values ordered lexically then you can enumerate them
> that way. Why force that behavior on people who want to order based
> on some other criteria?

Well, I was arguing about the default behavior. I'd bet that we're going
to have a constant set of people wondering why ORDER BY is doing the
'wrong thing' when ordering an ENUM, which is why I argued that the
default behavior should be ordering based on the external type, not how
we're storing it or some other order.

But I'm clearly in the minority in this view, so I'm droping it.

On to other issues...

Andrew, you mentioned that if you want to change the ordering you should
just create a new type. What about if you need to change the values that
are in the enum? MySQL does (or at least did, it's been some time since
I've messed with this) a horrible job at that. There's no way to rename
anything; you have to add the new names you want, then do a bulk update,
then delete the (now old) names. IMO this is broken.

Also, if we are going to maintain ordering and mapping (presumably via
the internal number that we're storing), then I think we should expose
that, at least optionally. So for example, you should be able to define
what a specific enum value means. Not everyone will want a linear
numbering starting at 0 afterall.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby wrote:

>Andrew, you mentioned that if you want to change the ordering you should
>just create a new type. What about if you need to change the values that
>are in the enum? MySQL does (or at least did, it's been some time since
>I've messed with this) a horrible job at that. There's no way to rename
>anything; you have to add the new names you want, then do a bulk update,
>then delete the (now old) names. IMO this is broken.
>
>


It would just be a standard "ALTER TABLE foo ALTER COLUMN bar TYPE
newtype USING expression" operation. You would write a function that
took a value of the old type and returned a value of the new type and
use a cll to that function in the expression. Since these would be named
types, unlike the case in mysql where they are anonymously defined
inline, this would present no difficulties at all.

>Also, if we are going to maintain ordering and mapping (presumably via
>the internal number that we're storing), then I think we should expose
>that, at least optionally. So for example, you should be able to define
>what a specific enum value means. Not everyone will want a linear
>numbering starting at 0 afterall.
>
>

What on earth for? Users should not care in the slightest what the
internal representation is . Users who want a map where the values are
exposed should create a lookup table.

You keep saying that we are using the internal representation as the
ordering. This is simply the wrong way to look at it. The internal
representation REFLECTS the ordering; it doesn't impose it. The user has
imposed the ordering when defining the type. In my enumkit I did provide
a function that gave back the internal representation, but I am not by
any means certain that that's a good idea.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Thu, Oct 27, 2005 at 09:45:05PM -0400, Andrew Dunstan wrote:
>
>
> Jim C. Nasby wrote:
>
> >Andrew, you mentioned that if you want to change the ordering you should
> >just create a new type. What about if you need to change the values that
> >are in the enum? MySQL does (or at least did, it's been some time since
> >I've messed with this) a horrible job at that. There's no way to rename
> >anything; you have to add the new names you want, then do a bulk update,
> >then delete the (now old) names. IMO this is broken.
> >
> >
>
>
> It would just be a standard "ALTER TABLE foo ALTER COLUMN bar TYPE
> newtype USING expression" operation. You would write a function that
> took a value of the old type and returned a value of the new type and
> use a cll to that function in the expression. Since these would be named
> types, unlike the case in mysql where they are anonymously defined
> inline, this would present no difficulties at all.

But why force a re-write of the entire table just to change the name of
something?

Or is ALTER COLUMN TYPE smart enough to not touch anything if the
mapping function is equality?

> >Also, if we are going to maintain ordering and mapping (presumably via
> >the internal number that we're storing), then I think we should expose
> >that, at least optionally. So for example, you should be able to define
> >what a specific enum value means. Not everyone will want a linear
> >numbering starting at 0 afterall.
> >
> >
>
> What on earth for? Users should not care in the slightest what the
> internal representation is . Users who want a map where the values are
> exposed should create a lookup table.
>
> You keep saying that we are using the internal representation as the
> ordering. This is simply the wrong way to look at it. The internal
> representation REFLECTS the ordering; it doesn't impose it. The user has
> imposed the ordering when defining the type. In my enumkit I did provide
> a function that gave back the internal representation, but I am not by
> any means certain that that's a good idea.

Well, someone was arguing that enum should be used as a convenient way
to map human labels on a set of values. To me, that means you should be
able to define exactly what that set of values is.

Personally, I don't see why enum can't just be syntactic sugar on top of
a side-table of values and a foreign key. And I guess a view to hide the
internals from normal viewing. That would certainly allow the most
flexibility, although it probably wouldn't perform as well as what you
wrote.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Ted Rolle wrote:

> This little snippet is great! The only problem I see is that the
> enums must be consistent across all modules.
>
> What about loading a variable with a "default" value? Then it could
> be adjusted to 'play'.
>
>

You can set a default for a variable using one of these types, as you
can for any other postgres type:

create table bar (color rainbow default 'blue');
insert into bar values(default);

As for any postgres type, the default must be a valid value for the type.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster