enums

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

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 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

Adding -hackers back to the list...

-----Original Message-----
From: Gregory Maxwell [mailto:gmaxwell@gmail.com]
Sent: Thursday, October 27, 2005 5:03 PM
To: Jim Nasby
Subject: Re: [HACKERS] enums

On 10/27/05, Jim C. Nasby <jnasby@pervasive.com> wrote:

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).

You could as equally say that it's ordering it by the order of the
enum declaration, which seems quite reasonable to me.

I don't really see why that's considered reasonable, especially as a default. I could maybe see an argument for having a means to see this ordering, but IMO anything depending on that is broken. I don't think we should be making any guarantees about how enums are stored in the database (including ordering).

Now, if you can multiply one enum with another or with an integer,
that would be a bit odd. But the collation behavior seems quite sane
to me.

The behavior in mysql is the same:

mysql> create table t (
-> color enum ('red','blue','green')
-> );
mysql> INSERT INTO t VALUES ('blue');
mysql> INSERT INTO t VALUES ('green');
mysql> INSERT INTO t VALUES ('red');
mysql> select color from t order by color;
+-------+
| color |
+-------+
| red |
| blue |
| green |
+-------+
3 rows in set (0.04 sec)

Hopefully we the end implimentation is a bit better than mysql and
actually cares what you're inserting:

mysql> select color*2 from t;
+---------+
| color*2 |
+---------+
| 4 |
| 6 |
| 2 |
+---------+
3 rows in set (0.00 sec)
mysql> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t VALUES ('monkey');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select color from t;
+-------+
| color |
+-------+
| blue |
| green |
| red |
| red |
| |
+-------+
5 rows in set (0.00 sec)

Your examples show why I don't think it's a good idea to use MySQL as a guide for how to do enums.

If we do decide to include the concept of ordering in enums, then it should be fully supported and not just an artifact of our storage mechanism. This means supporting things like being able to re-order the accepted values in an enum. But like I said, I just don't see the use case for doing that.

On 10/27/05, Jim Nasby <jnasby@pervasive.com> wrote:

Adding -hackers back to the list...

You could as equally say that it's ordering it by the order of the
enum declaration, which seems quite reasonable to me.

I don't really see why that's considered reasonable, especially as a default. I could maybe see an argument for having a means to see this ordering, but IMO anything depending on that is broken. I don't think we should be making any guarantees about how enums are stored in the database (including ordering).

Your examples show why I don't think it's a good idea to use MySQL as a guide for how to do enums.

Yes, MySQL is broken in some regards, as usual. However, the API isn't
bad (except for the fact that it doesn't care what invalid crap you
throw at it), and more importantly there are thousands of apps and
developers who think around that interface. We should copy it without
the brokenness as much as possible unless we have good cause
otherwise.

If we do decide to include the concept of ordering in enums, then it should be fully supported and not just an artifact of our storage mechanism. This means supporting things like being able to re-order the accepted values in an enum. But like I said, I just don't see the use case for doing that.

So what do you propose we do for a default ordering? I hope you don't
think we should force a sort as though the enum labels were text...
That almost certainly incorrect for most applications of enums, which
are used to make opaque labels more human compatible.

MySQL's behavior of allowing the user to specify the collation in the
typedef makes a lot of sense to me, it doesn't matter that it actually
works as an artifact of the storage backend. I'd argue that it would
make sense to sort by the specification order even if we changed the
backend to use varchars rather than numbers.

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

On Thu, Oct 27, 2005 at 06:46:24PM -0400, Gregory Maxwell wrote:

So what do you propose we do for a default ordering? I hope you don't
think we should force a sort as though the enum labels were text...

I do think that. Or default ordering on whatever type the enum is (I can
see enums that are something other than text as useful, though that's a
secondary goal).

That almost certainly incorrect for most applications of enums, which
are used to make opaque labels more human compatible.

Sorting red before blue doesn't sound very opaque to me...

MySQL's behavior of allowing the user to specify the collation in the
typedef makes a lot of sense to me, it doesn't matter that it actually
works as an artifact of the storage backend. I'd argue that it would
make sense to sort by the specification order even if we changed the
backend to use varchars rather than numbers.

Like I said, if we're going to support a concept of ordering of items in
an enum then we need to support it fully. For starters that means having
the ability to re-order things in an enum seamlessly.

If our primary concern is MySQL compatability then we should look at
offering two types of enums; one that mirrors their broken stuff and one
that works they way you'd actually want it to.
--
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

Gregory Maxwell wrote:

Yes, MySQL is broken in some regards, as usual. However, the API isn't
bad (except for the fact that it doesn't care what invalid crap you
throw at it), and more importantly there are thousands of apps and
developers who think around that interface. We should copy it without
the brokenness as much as possible unless we have good cause
otherwise.

mmm ... no. It is too broken. We should do enums orthogonally with other
type definitions in PostgreSQL. Where I would like to get to is that we
have a flavor of CREATE TYPE that will create the enum type for us,
including all the support that I build into my little kit. And if you
want to change the enumeration set on a column, you would use ALTER
TABLE foo ALTER COLUMN bar TYPE newtype USING ...

Inline declarations of enums does not strike me as good.

If we do decide to include the concept of ordering in enums, then it should be fully supported and not just an artifact of our storage mechanism. This means supporting things like being able to re-order the accepted values in an enum. But like I said, I just don't see the use case for doing that.

So what do you propose we do for a default ordering? I hope you don't
think we should force a sort as though the enum labels were text...
That almost certainly incorrect for most applications of enums, which
are used to make opaque labels more human compatible.

Yeah, lexical ordering is surely wrong. I believe that the enumeration
order is the only correct ordering. If you wanted lexical ordering, you
could always do "order by myenum::text".

MySQL's behavior of allowing the user to specify the collation in the
typedef makes a lot of sense to me, it doesn't matter that it actually
works as an artifact of the storage backend. I'd argue that it would
make sense to sort by the specification order even if we changed the
backend to use varchars rather than numbers.

Can't see the point in it, I must confess.

cheers

andrew

Jim C. Nasby wrote:

Like I said, if we're going to support a concept of ordering of items in
an enum then we need to support it fully. For starters that means having
the ability to re-order things in an enum seamlessly.

I do not see this at all. An enumeration defines an ordering and a set
of labels. Why should you be able to change it? If you want a different
ordering, create a new enumeration. Let's do this right because it's a
feature worth having, not just mimic the competition's idiocy.

cheers

andrew

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

On Thursday 2005-10-27 16:22, Andrew Dunstan wrote:

Jim C. Nasby wrote:

Like I said, if we're going to support a concept of ordering of items in
an enum then we need to support it fully. For starters that means having
the ability to re-order things in an enum seamlessly.

I do not see this at all. An enumeration defines an ordering and a set
of labels. Why should you be able to change it? If you want a different
ordering, create a new enumeration. Let's do this right because it's a
feature worth having, not just mimic the competition's idiocy

The symbols in the set have no _per se_ order.
A collation rule is necessary to sort the symbols consistently.
ASCII is an enumeration
Unicode is a large enumeration with a simple naive collation and a complex
default collation.

Defining a set results in an unordered specification of symbols.
Defining a collation produces an ordering for the set.
There can be many collations for a set.

An enumeration is just a computer science short-hand way to define a set and a
"native" collation for the set.
An enumeration's native collation need not be the only, or even the most
common, collation for the enumerated set of symbols.

On Oct 28, 2005, at 9:23 , Trent Shipley wrote:

On Thursday 2005-10-27 16:22, Andrew Dunstan wrote:

Jim C. Nasby wrote:

Like I said, if we're going to support a concept of ordering of
items in
an enum then we need to support it fully. For starters that means
having
the ability to re-order things in an enum seamlessly.

I do not see this at all. An enumeration defines an ordering and a
set
of labels. Why should you be able to change it? If you want a
different
ordering, create a new enumeration. Let's do this right because
it's a
feature worth having, not just mimic the competition's idiocy

The symbols in the set have no _per se_ order.
A collation rule is necessary to sort the symbols consistently.
ASCII is an enumeration
Unicode is a large enumeration with a simple naive collation and a
complex
default collation.

Defining a set results in an unordered specification of symbols.
Defining a collation produces an ordering for the set.
There can be many collations for a set.

An enumeration is just a computer science short-hand way to define
a set and a
"native" collation for the set.
An enumeration's native collation need not be the only, or even the
most
common, collation for the enumerated set of symbols.

Relational databases already have a type for unordered sets: tables.
IMO, if there's going to be a separate enumerated type, it should be
more than just an alternative way of defining a set of key-value pairs.

Michael Glaesemann
grzm myrealbox com

Trent Shipley wrote:

An enumeration is just a computer science short-hand way to define a set and a
"native" collation for the set.
An enumeration's native collation need not be the only, or even the most
common, collation for the enumerated set of symbols.

No it's not. Many languages define enumerated types as having a fixed
ordering. It is not just a set. That is the sense in which I am using
the term. And, after all, you can always write a function that gives you
an alternative ordering. All we are building in is an ordering based in
the enumeration order, which you are at perfect liberty not to use.
Postgres lets you define an alternative operator class for any type, so
we are very flexible. People are getting way too hung up over this.

cheers

andrew

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

What about use the declaration order as the enum order?, for example: if I
declare something like: "CREATE ENUM hola ('item1', 'item3', 'item2');"
-this is just assuming an hypothetical approach to use enum types in this
way- and the logical order of the items could be 'item1', 'item3', 'item2'
just because the user decided to create them in this way.

Well, that's just an idea...

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andrew Dunstan
Sent: Jueves, 27 de Octubre de 2005 07:06 p.m.
To: tshipley@deru.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] enums

Trent Shipley wrote:

An enumeration is just a computer science short-hand way to define a set

and a

"native" collation for the set.
An enumeration's native collation need not be the only, or even the most
common, collation for the enumerated set of symbols.

No it's not. Many languages define enumerated types as having a fixed
ordering. It is not just a set. That is the sense in which I am using
the term. And, after all, you can always write a function that gives you
an alternative ordering. All we are building in is an ordering based in
the enumeration order, which you are at perfect liberty not to use.
Postgres lets you define an alternative operator class for any type, so
we are very flexible. People are getting way too hung up over this.

cheers

andrew

---------------------------(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

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

Cristian Prieto wrote:

What about use the declaration order as the enum order?, for example: if I
declare something like: "CREATE ENUM hola ('item1', 'item3', 'item2');"
-this is just assuming an hypothetical approach to use enum types in this
way- and the logical order of the items could be 'item1', 'item3', 'item2'
just because the user decided to create them in this way.

Well, that's just an idea...

Using the declaration order is exactly what I have done.

cheers

andrew

On Thursday 2005-10-27 17:39, Michael Glaesemann wrote:

On Oct 28, 2005, at 9:23 , Trent Shipley wrote:

On Thursday 2005-10-27 16:22, Andrew Dunstan wrote:

Jim C. Nasby wrote:

<snip/>

Relational databases already have a type for unordered sets: tables.
IMO, if there's going to be a separate enumerated type, it should be
more than just an alternative way of defining a set of key-value pairs.

Michael Glaesemann
grzm myrealbox com

Of course, what is an enumeration except an *ordered* list of key-value pairs;
that is, a set with a built-in collation.

Glaesemann is right. To me that implies that no database NEEDS an enumeration
type.

We just started discussing it because it would greatly enhance MySQL
migration. Even more important, enumerations would be tremendously
convenient (even if they are not strictly necessary).

Enumerations would be good marketing and good engineering.

The debate is about implementation:

1) Pure list (seems to be why MySQL does). Each enumeration is a list. If
you want to create a variant collation for the list, create a new, parallel
list.

This is very straightforward but violates the "store once, read many times"
principle of database design.

2) Hybrid list + secondary collations. You create and store an enumeration
(call it enum_a). If you want to reorder the underlying set, just declare a
new collation for the enumeration (call the result enum_b). enum_b is
effectively a virtual enumeration. The relationship of enum_b to enum_a is
like that between a view and its table.

On the downside, this approach is a theoretical stew. It should be relatively
easy to implement.

3) Set + collation functions. You define a set. You define a collation for
the set. Having declared set_a and a collation_a you can then declare
enumeration_a. (Note that the result defined by the developer's collation
function may not necessarily result in a _per se_ enumeration.)

This has the appeal of separating the symbol declaration from its ordering.
Furthermore, of all the options it is the most powerful. Unfortunately, it
may be verbose, unintuitive, and the most difficult to implement.

There is probably no reason approach #1 or #2 could not be implemented using
the machinery for approach #3 under the bonnet. That way we could have
something like:

CREATE SYMBOL SET
{possibly a disguised create table, but probably not for performance
reasons}
CREATE COLLATION USING function_name
CREATE ENUMERATION
and
CREATE MYSQL_ENUMERATION. {probably just overload CREATE ENUMERATION}

===============================

http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html
http://dev.mysql.com/doc/refman/5.1/en/enum.html
(Note that unlike C enumerations MySql enumerations are two way and do some
context dependent magic.)

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