Thread: enum bug

enum bug

From

Elein

Date:

12 March 2016, 00:36:49

> Bug/Improvement:
>
>    Enums belong to types, not to column values.
>
>    * Create a built in function, like enum_range( type ), independent of tables
>      that shows a type's enum values.
>        create or replace function enum_range( typein regtype ) returns name[] language sql as
>        $$
>        select array_agg(enumlabel) from
>            (select enumlabel
>            from pg_enum e join pg_type t on (e.enumtypid = t.oid)
>            where t.typtype = 'e' and t.oid = typein
>            order by enumsortorder
>            ) foo;
>        $$;
>    * When an insert into an enum column fails give the person a hint as to valid values
>    * Make enum_range to not be dependent on values in the target table.
>      Remove/Obsolete enum_range( enum_column ) and replace with enum_range( typein regtype )
>
>    Workaround: define the enum_range( typein regtyp ) yourself.
>
>
> create type rainbow as enum ('red','orange','yellow','blue','purple');
> CREATE TYPE
> create table badinfo (color rainbow);
> CREATE TABLE
>
> -- Lousy message.  Show enum list.
> insert into badinfo values ('green');
> ERROR:  invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');
>
>
> -- Lousy message.
> select enum_range(color) from foo;
> enum_range
> ------------
> (0 rows)
>
> insert into foo values ('red');
> INSERT 0 1
> insert into foo values ('blue');
> INSERT 0 1
>
> -- Ooh.  Only shows the enum list for valid value in a table.
> select enum_range(color) from foo;
>           enum_range
> ---------------------------------
> {red,orange,yellow,blue,purple}
> {red,orange,yellow,blue,purple}
> (2 rows)
Elein Mustain
elein@varlena.com
510-637-9106

Re: enum bug

From

Melvin Davidson

Date:

12 March 2016, 00:56:37

On Fri, Mar 11, 2016 at 4:36 PM, Elein <elein@varlena.com> wrote:

> Bug/Improvement:
>
> Enums belong to types, not to column values.
>
> * Create a built in function, like enum_range( type ), independent of tables
> that shows a type's enum values.
> create or replace function enum_range( typein regtype ) returns name[] language sql as
> $$
> select array_agg(enumlabel) from
> (select enumlabel
> from pg_enum e join pg_type t on (e.enumtypid = t.oid)
> where t.typtype = 'e' and t.oid = typein
> order by enumsortorder
> ) foo;
> $$;
> * When an insert into an enum column fails give the person a hint as to valid values
> * Make enum_range to not be dependent on values in the target table.
> Remove/Obsolete enum_range( enum_column ) and replace with enum_range( typein regtype )
>
> Workaround: define the enum_range( typein regtyp ) yourself.
>
>
> create type rainbow as enum ('red','orange','yellow','blue','purple');
> CREATE TYPE
> create table badinfo (color rainbow);
> CREATE TABLE
>
> -- Lousy message. Show enum list.
> insert into badinfo values ('green');
> ERROR: invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');
>
>
> -- Lousy message.
> select enum_range(color) from foo;
> enum_range
> ------------
> (0 rows)
>
> insert into foo values ('red');
> INSERT 0 1
> insert into foo values ('blue');
> INSERT 0 1
>
> -- Ooh. Only shows the enum list for valid value in a table.
> select enum_range(color) from foo;
> enum_range
> ---------------------------------
> {red,orange,yellow,blue,purple}
> {red,orange,yellow,blue,purple}
> (2 rows)
Elein Mustain
elein@varlena.com
510-637-9106

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Enums are evil!
http://www.lornajane.net/posts/2010/is-enum-evil

enums are from before there were foreign keys
You are better off just making a foreign key contraint.
Things will be a lot simpler.
eg:
    CREATE TABLE rainbow_colors
    ( valid_color varchar(15),
    CONSTRAINT rainbow_colors_pk PRIMARY KEY(valid_color)
    );

    CREATE TABLE badinfo
    ( color varchar(15),
    CONSTRAINT badinfo_pk PRIMARY KEY (color),
    CONSTRAINT badinfo_valid FOREIGN KEY (color)
        REFERENCES rainbow_colors (valid_color)
    );
--

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: enum bug

From

John R Pierce

Date:

12 March 2016, 01:03:33

On 3/11/2016 1:36 PM, Elein wrote:
>> >Bug/Improvement:
>> >
>> >    Enums belong to types, not to column values....

I've read this post twice, and I'm still unclear on what you're saying
is a problem, and what your proposed solution is...




--
john r pierce, recycling bits in santa cruz

Re: enum bug

From

"David G. Johnston"

Date:

12 March 2016, 01:07:02

On Fri, Mar 11, 2016 at 2:36 PM, Elein <elein@varlena.com> wrote:

> Bug/Improvement:
>
> Enums belong to types, not to column values.
>
> * Create a built in function, like enum_range( type ), independent of tables
> that shows a type's enum values.
> create or replace function enum_range( typein regtype ) returns name[] language sql as
> $$
> select array_agg(enumlabel) from
> (select enumlabel
> from pg_enum e join pg_type t on (e.enumtypid = t.oid)
> where t.typtype = 'e' and t.oid = typein
> order by enumsortorder
> ) foo;
> $$;
> * When an insert into an enum column fails give the person a hint as to valid values
> * Make enum_range to not be dependent on values in the target table.
> Remove/Obsolete enum_range( enum_column ) and replace with enum_range( typein regtype )
>
> Workaround: define the enum_range( typein regtyp ) yourself.
>
>
> create type rainbow as enum ('red','orange','yellow','blue','purple');
> CREATE TYPE
> create table badinfo (color rainbow);
> CREATE TABLE
>

> -- Lousy message. Show enum list.
> insert into badinfo values ('green');
> ERROR: invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');
>
>
> -- Lousy message.
> select enum_range(color) from foo;
> enum_range
> ------------
> (0 rows)
>
> insert into foo values ('red');
> INSERT 0 1
> insert into foo values ('blue');
> INSERT 0 1
>
> -- Ooh. Only shows the enum list for valid value in a table.
> select enum_range(color) from foo;
> enum_range
> ---------------------------------
> {red,orange,yellow,blue,purple}
> {red,orange,yellow,blue,purple}
> (2 rows)
Elein Mustain
elein@varlena.com
510-637-9106

Just reading here...

I don't really understand what point you are trying to make but:

SELECT enum_range(color) FROM foo;

enum_range() will be executed once for every row in foo. A row can only exist in foo if either color is a valid rainbow enum or NULL.

f you simply want to execute enum_range(rainbow) you can do something as simple as:

SELECT enum_range(null::rainbow);

I do see that there could be value in having:

> -- Lousy message. Show enum list.

> insert into badinfo values ('green');
> ERROR: invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');

add a HINT: to the output listing all labels for the enum

If that is your main point you did a good job of hiding in in a bunch of other stuff that is only tangentially related. Otherwise I still have no idea what exactly is the point you are trying to make.

What version are you using anyway?

David J.

Re: enum bug

From

"David G. Johnston"

Date:

12 March 2016, 01:11:45

On Fri, Mar 11, 2016 at 2:55 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

Enums are evil!
http://www.lornajane.net/posts/2010/is-enum-evil

Using red background is evil :)

I'll disagree on the premise but will agree that without a better implementation and handling of change our implementation makes using them a somewhat risky prospect (in so far as future you or someone else may considered evil acts when business requirements change).

David J.

Re: enum bug

From

Elein

Date:

12 March 2016, 02:19:29

The point is that enum information belongs to a type, not a column value of that type.

This is the difference between a class and a class instance.

If you get that, you understand.

The workaround suggested only works if some non-empty row in some table has a column defined to be that enum type.

An unused (yet) enum type cannot display the enum ranges. An empty table containing that type cannot display enum ranges.

The example selects were what I did to figure out that enum_ranges only worked on existing data. Sorry if they were confusing. But the way enum values are currently displayed is confusing.

Enums are evil because of the difficulty working with them. They should be used for a fixed set of valid values. Otherwise use a lookup table.

Elein Mustain

elein@varlena.com

510-637-9106

On Mar 11, 2016, at 2:06 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Mar 11, 2016 at 2:36 PM, Elein <elein@varlena.com> wrote:

> Bug/Improvement:
>
> Enums belong to types, not to column values.
>
> * Create a built in function, like enum_range( type ), independent of tables
> that shows a type's enum values.
> create or replace function enum_range( typein regtype ) returns name[] language sql as
> $$
> select array_agg(enumlabel) from
> (select enumlabel
> from pg_enum e join pg_type t on (e.enumtypid = t.oid)
> where t.typtype = 'e' and t.oid = typein
> order by enumsortorder
> ) foo;
> $$;
> * When an insert into an enum column fails give the person a hint as to valid values
> * Make enum_range to not be dependent on values in the target table.
> Remove/Obsolete enum_range( enum_column ) and replace with enum_range( typein regtype )
>
> Workaround: define the enum_range( typein regtyp ) yourself.
>
>
> create type rainbow as enum ('red','orange','yellow','blue','purple');
> CREATE TYPE
> create table badinfo (color rainbow);
> CREATE TABLE
>

> -- Lousy message. Show enum list.
> insert into badinfo values ('green');
> ERROR: invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');
>
>
> -- Lousy message.
> select enum_range(color) from foo;
> enum_range
> ------------
> (0 rows)
>
> insert into foo values ('red');
> INSERT 0 1
> insert into foo values ('blue');
> INSERT 0 1
>
> -- Ooh. Only shows the enum list for valid value in a table.
> select enum_range(color) from foo;
> enum_range
> ---------------------------------
> {red,orange,yellow,blue,purple}
> {red,orange,yellow,blue,purple}
> (2 rows)
Elein Mustain
elein@varlena.com
510-637-9106

Just reading here...

I don't really understand what point you are trying to make but:

SELECT enum_range(color) FROM foo;

enum_range() will be executed once for every row in foo. A row can only exist in foo if either color is a valid rainbow enum or NULL.

I
f you simply want to execute enum_range(rainbow) you can do something as simple as:

SELECT enum_range(null::rainbow);

I do see that there could be value in having:

> -- Lousy message. Show enum list.
> insert into badinfo values ('green');
> ERROR: invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');

add a HINT: to the output listing all labels for the enum

If that is your main point you did a good job of hiding in in a bunch of other stuff that is only tangentially related. Otherwise I still have no idea what exactly is the point you are trying to make.

What version are you using anyway?

David J.

Re: enum bug

From

"David G. Johnston"

Date:

12 March 2016, 02:46:02

On Fri, Mar 11, 2016 at 4:19 PM, Elein <elein@varlena.com> wrote:

An unused (yet) enum type cannot display the enum ranges. An empty table containing that type cannot display enum ranges.

Yes, it can.

CREATE TYPE rainbow AS enum ('red','orange','yellow','blue','purple');

SELECT enum_range(null::rainbow);

enum_range

{red,orange,yellow,blue,purple}

I get the distinction between classes and objects. But in many cases, like this one, you need to obtain an instance of a class - a null is generally sufficient - and pass that instance to a function. The function can then use "pg_typeof(instance_value)::oid" to derive the oid for the corresponding class. This is a common idiom in PostgreSQL.

The only improvement, besides the error handling point, that I see to be had here is your understanding of how the system works.

David J.

Re: enum bug

From

Elein

Date:

12 March 2016, 05:23:10

Elein Mustain

elein@varlena.com

510-637-9106

On Mar 11, 2016, at 3:45 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Mar 11, 2016 at 4:19 PM, Elein <elein@varlena.com> wrote:
An unused (yet) enum type cannot display the enum ranges. An empty table containing that type cannot display enum ranges.

Yes, it can.

CREATE TYPE rainbow AS enum ('red','orange','yellow','blue','purple');
SELECT enum_range(null::rainbow);
enum_range
{red,orange,yellow,blue,purple}

Yes you are right about this point. It is a workaround for poor syntax sugar design.

I get the distinction between classes and objects. But in many cases, like this one, you need to obtain an instance of a class - a null is generally sufficient - and pass that instance to a function. The function can then use "pg_typeof(instance_value)::oid" to derive the oid for the corresponding class. This is a common idiom in PostgreSQL.

This is not a common idiom of postgres according to the ORDBMS model.

The only improvement, besides the error handling point, that I see to be had here is your understanding of how the system works.

I know how it was designed to work. Been there done that.

David J.

Re: enum bug

From

Alvaro Herrera

Date:

14 March 2016, 05:08:42

Melvin Davidson wrote:

> Enums are evil!
> http://www.lornajane.net/posts/2010/is-enum-evil

???

This post is about MySQL's enums, which aren't really related to
Postgres enums:

    "In order to change the allowed values of an enum column, we
    need to issue an alter table statement [...]   Alter table
    actually creates a new table matching the new structure, copies
    all the data across, and then renames the new table to the right
    name."

This is not at all how things happen in Postgres' enums.


> enums are from before there were foreign keys

In Postgres, ENUMs are pretty recent actually -- a lot newer than FKs:

commit 57690c6803525f879fe96920a05e979ece073e71
Author:     Tom Lane <tgl@sss.pgh.pa.us>
AuthorDate: Mon Apr 2 03:49:42 2007 +0000
CommitDate: Mon Apr 2 03:49:42 2007 +0000

    Support enum data types.  Along the way, use macros for the values of
    pg_type.typtype whereever practical.  Tom Dunstan, with some kibitzing
    from Tom Lane.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: enum bug

From

Melvin Davidson

Date:

14 March 2016, 05:20:50

The post refers to the generic unmanageability of enums in genaral, it just uses MySQL as a reference basis.

Google evil enum and you will find several articles that all say the same thing.

To be specific,even in PostgreSQL, there is no easy way to delete enum values once they exist, other than playing with system catalogs.

The point is, they are an archaic data type and it's a hell of a lot easier to use Foreign Keys to insure integrity.

I've seen systems designed with an enum that has over 20 values associated with it. It's insane to do that.

On Sun, Mar 13, 2016 at 10:08 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

Melvin Davidson wrote:

> Enums are evil!
> http://www.lornajane.net/posts/2010/is-enum-evil

???

This post is about MySQL's enums, which aren't really related to
Postgres enums:

"In order to change the allowed values of an enum column, we
need to issue an alter table statement [...] Alter table
actually creates a new table matching the new structure, copies
all the data across, and then renames the new table to the right
name."

This is not at all how things happen in Postgres' enums.

> enums are from before there were foreign keys

In Postgres, ENUMs are pretty recent actually -- a lot newer than FKs:

commit 57690c6803525f879fe96920a05e979ece073e71
Author: Tom Lane <tgl@sss.pgh.pa.us>
AuthorDate: Mon Apr 2 03:49:42 2007 +0000
CommitDate: Mon Apr 2 03:49:42 2007 +0000

Support enum data types. Along the way, use macros for the values of
pg_type.typtype whereever practical. Tom Dunstan, with some kibitzing
from Tom Lane.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: enum bug

From

Alvaro Herrera

Date:

14 March 2016, 05:22:15

Elein wrote:

Hi Elein,

>    * When an insert into an enum column fails give the person a hint as to valid values

> -- Lousy message.  Show enum list.
> insert into badinfo values ('green');
> ERROR:  invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');

True, we could improve that, though it could easily get messy with large
enums.

> >    * Make enum_range to not be dependent on values in the target table.
> >      Remove/Obsolete enum_range( enum_column ) and replace with enum_range( typein regtype )
> >
> >    Workaround: define the enum_range( typein regtyp ) yourself.

Hmm, this is pretty clunky.  I don't think passing the OID of the enum
itself is a lot better, but if you use a regtype cast then perhaps it's
not that bad.  Perhaps we could have both functions.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: enum bug

From

"Joshua D. Drake"

Date:

14 March 2016, 18:25:34

On 03/11/2016 03:19 PM, Elein wrote:

> An unused (yet) enum type cannot display the enum ranges. An empty table
> containing that type cannot display enum ranges.
>
> The example selects were what I did to figure out that enum_ranges only
> worked on existing data. Sorry if they were confusing. But the way enum
> values are currently displayed is confusing.
>
> Enums are evil because of the difficulty working with them. They should
> be used for a fixed set of valid values. Otherwise use a lookup table.

"Otherwise use a lookup table."

That is the solution to this problem.

JD

--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

Re: enum bug

From

Alvaro Herrera

Date:

14 March 2016, 18:48:20

Joshua D. Drake wrote:
> On 03/11/2016 03:19 PM, Elein wrote:
>
> >An unused (yet) enum type cannot display the enum ranges. An empty table
> >containing that type cannot display enum ranges.
> >
> >The example selects were what I did to figure out that enum_ranges only
> >worked on existing data. Sorry if they were confusing. But the way enum
> >values are currently displayed is confusing.
> >
> >Enums are evil because of the difficulty working with them. They should
> >be used for a fixed set of valid values. Otherwise use a lookup table.
>
> "Otherwise use a lookup table."
>
> That is the solution to this problem.

You know, I suspect this Elein person may know a bit about these
database thingies(*).

I don't think she is complaining because she cannot find her way around
designing a garment color table; rather it seems to me she is pointing
out actual problems in our design of the enum feature because she would
like to see it improve.  If improved enough, maybe we could get to a
point where they could actually be used; otherwise why the heck did we
let the feature in the database in the first place?  I think all these
"use a lookup table, you silly!" answers are missing the point.

(*) Yes, I'm being a bit sarcastic here, sorry about that.  I actually
learned quite a bit of database design and related topics by translating
the "General Bits" column she used to write, many years ago.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: enum bug

From

"Joshua D. Drake"

Date:

14 March 2016, 18:52:21

On 03/14/2016 08:48 AM, Alvaro Herrera wrote:
> Joshua D. Drake wrote:
>> On 03/11/2016 03:19 PM, Elein wrote:
>>
>>> An unused (yet) enum type cannot display the enum ranges. An empty table
>>> containing that type cannot display enum ranges.
>>>
>>> The example selects were what I did to figure out that enum_ranges only
>>> worked on existing data. Sorry if they were confusing. But the way enum
>>> values are currently displayed is confusing.
>>>
>>> Enums are evil because of the difficulty working with them. They should
>>> be used for a fixed set of valid values. Otherwise use a lookup table.
>>
>> "Otherwise use a lookup table."
>>
>> That is the solution to this problem.
>
> You know, I suspect this Elein person may know a bit about these
> database thingies(*).

I am fully aware of who Elein is.

>
> I don't think she is complaining because she cannot find her way around
> designing a garment color table; rather it seems to me she is pointing
> out actual problems in our design of the enum feature because she would
> like to see it improve.

And what I am saying, is just use a lookup table instead. If you want to
spend your valuable time on ENUM design, go for it. Nobody is stopping
you or suggesting *you* shouldn't.

>  If improved enough, maybe we could get to a
> point where they could actually be used; otherwise why the heck did we
> let the feature in the database in the first place?  I think all these
> "use a lookup table, you silly!" answers are missing the point.

1. I thought ENUMS were not needed in the first place. I still do.

2. I never called anyone silly, nor was I derogatory or dismissive. I
offered, my solution to this problem because ENUMS are a pain in the
butt and have been since the original implementation. I have yet to see
a valid reason to use them instead of a lookup table. I have even
written articles about the three basic solutions:

https://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/

>
> (*) Yes, I'm being a bit sarcastic here, sorry about that.  I actually
> learned quite a bit of database design and related topics by translating
> the "General Bits" column she used to write, many years ago.
>

Your answer presumes some personal issue with Elein. I don't know why
that is. I gave a technical answer to a technical problem.

Sincerely,

JD

--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

Re: enum bug

From

"David G. Johnston"

Date:

14 March 2016, 18:53:39

On Mon, Mar 14, 2016 at 8:48 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

Joshua D. Drake wrote:
> On 03/11/2016 03:19 PM, Elein wrote:
>
> >An unused (yet) enum type cannot display the enum ranges. An empty table
> >containing that type cannot display enum ranges.
> >
> >The example selects were what I did to figure out that enum_ranges only
> >worked on existing data. Sorry if they were confusing. But the way enum
> >values are currently displayed is confusing.
> >
> >Enums are evil because of the difficulty working with them. They should
> >be used for a fixed set of valid values. Otherwise use a lookup table.
>
> "Otherwise use a lookup table."
>
> That is the solution to this problem.

You know, I suspect this Elein person may know a bit about these
database thingies(*).

I don't think she is complaining because she cannot find her way around
designing a garment color table; rather it seems to me she is pointing
out actual problems in our design of the enum feature because she would
like to see it improve. If improved enough, maybe we could get to a
point where they could actually be used; otherwise why the heck did we
let the feature in the database in the first place? I think all these
"use a lookup table, you silly!" answers are missing the point.

I still cannot figure out what the actual problem is that a lookup table is the solution.

We already have a way to show all the possible enum values for a given enum independent of whether it is use in a table or not.

The fact that we requiring passing a null instance instead of an OID might be unusual but our recent json_to_record stuff does the exact same thing so unusually or not it is (now?) idiomatic PostgreSQL.

David J.

Re: enum bug

From

"David G. Johnston"

Date:

14 March 2016, 19:02:30

On Mon, Mar 14, 2016 at 8:52 AM, Joshua D. Drake <jd@commandprompt.com> wrote:

If improved enough, maybe we could get to a
point where they could actually be used; otherwise why the heck did we
let the feature in the database in the first place? I think all these
"use a lookup table, you silly!" answers are missing the point.

1. I thought ENUMS were not needed in the first place. I still do.

2. I never called anyone silly, nor was I derogatory or dismissive. I offered, my solution to this problem because ENUMS are a pain in the butt and have been since the original implementation. I have yet to see a valid reason to use them instead of a lookup table. I have even written articles about the three basic solutions:

https://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/

The one nice thing about enums is that you get two concepts in one column - a human readable label and a system used ordering.

i.e., "SELECT enum_value FROM tbl ORDER BY enum_value" actually

gives you a meaningful order without having to carry around, or relink to, a lookup table to get an ordering column.

Now, this is a bit overrated since you immediately lose that ability if you export to a Spreadsheet program, or otherwise lose the ordering nature during a convert-to-text operation.

David J.

Re: enum bug

From

"Joshua D. Drake"

Date:

14 March 2016, 19:07:53

On 03/14/2016 09:02 AM, David G. Johnston wrote:

> The one nice thing about enums is that you get two concepts in one
> column - a human readable label and a system used ordering.
>
> i.e., "SELECT enum_value FROM tbl ORDER BY enum_value" actually
> 
> gives you a meaningful order without having to carry around, or relink
> to, a lookup table to get an ordering column.
>
> Now, this is a bit overrated since you immediately lose that ability if
> you export to a Spreadsheet program, or otherwise lose the ordering
> nature during a convert-to-text operation.
>

I do not suggest that ENUMS are useless just that there are more
flexible and reasonable ways (as a whole) to do what ENUMS provide.

Sincerely,

JD

--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

Re: enum bug

From

Melvin Davidson

Date:

14 March 2016, 19:10:17

On Mon, Mar 14, 2016 at 12:07 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 03/14/2016 09:02 AM, David G. Johnston wrote:

The one nice thing about enums is that you get two concepts in one
column - a human readable label and a system used ordering.

i.e., "SELECT enum_value FROM tbl ORDER BY enum_value" actually

gives you a meaningful order without having to carry around, or relink
to, a lookup table to get an ordering column.

Now, this is a bit overrated since you immediately lose that ability if
you export to a Spreadsheet program, or otherwise lose the ordering
nature during a convert-to-text operation.

I do not suggest that ENUMS are useless just that there are more flexible and reasonable ways (as a whole) to do what ENUMS provide.

Sincerely,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

It's not that hard to get all the valid enum values for a particular enum type.
Either of the queries below works just fine.

SELECT e.enumtypid,
       e.enumlabel,
       e.enumsortorder
FROM pg_enum e
WHERE e.enumtypid = {the enum oid}
ORDER BY 1, enumsortorder;

SELECT e.enumlabel,
       e.enumsortorder,
       e.enumtypid
FROM pg_type t
JOIN pg_enum e ON e.enumtypid = t.oid
WHERE t.typtype = 'e'
AND t.typname = {the enum name}
ORDER BY 1, enumsortorder;

However, IMHO, enums are archaic and Foreign Keys (Parent/Child) is the better way to go.
--

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: enum bug

From

Andrew Sullivan

Date:

14 March 2016, 19:43:50

On Sun, Mar 13, 2016 at 10:20:05PM -0400, Melvin Davidson wrote:
> The point is, they are an archaic data type and it's a hell of a lot easier
> to use Foreign Keys to insure integrity.

So don't use them?  Nobody, surely, is forcing you to use enums.

I recall when enums were added.  I recall thinking at the time that
they were a bad idea and that you should do such things properly with
CHECK constraints and so on.  But people wanted them because lots of
(IMO poor) designs rely on them.  That seems like a good reason to me
to keep them around, and not to use them :)

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Re: enum bug

From

Alvaro Herrera

Date:

14 March 2016, 19:54:50

Joshua D. Drake wrote:
> On 03/14/2016 08:48 AM, Alvaro Herrera wrote:

> >(*) Yes, I'm being a bit sarcastic here, sorry about that.  I actually
> >learned quite a bit of database design and related topics by translating
> >the "General Bits" column she used to write, many years ago.
>
> Your answer presumes some personal issue with Elein. I don't know why that
> is.

Quite the contrary.  I hold the utmost respect for her and her work.  I
think she has something useful to say on this topic (and many others)
and the community is being myopic by saying that "enums are useless
anyway, so why improve on them, use lookup tables" which is essentially
what is being repeated over and over.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: enum bug

From

Elein

Date:

14 March 2016, 21:00:39


Elein Mustain
elein@varlena.com
510-637-9106

> On Mar 13, 2016, at 7:22 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> Elein wrote:
>
> Hi Elein,
>
>>   * When an insert into an enum column fails give the person a hint as to valid values
>
>> -- Lousy message.  Show enum list.
>> insert into badinfo values ('green');
>> ERROR:  invalid input value for enum rainbow: "green"
>> LINE 1: insert into badinfo values ('green');
>
> True, we could improve that, though it could easily get messy with large
> enums.
>
>
>>>   * Make enum_range to not be dependent on values in the target table.
>>>     Remove/Obsolete enum_range( enum_column ) and replace with enum_range( typein regtype )
>>>
>>>   Workaround: define the enum_range( typein regtyp ) yourself.
>
> Hmm, this is pretty clunky.  I don't think passing the OID of the enum
> itself is a lot better, but if you use a regtype cast then perhaps it's
> not that bad.  Perhaps we could have both functions.

I was thinking of this function in terms of the catalog functions like pg_get_functiondef which takes an oid. Obviosly
allof those functions can take  reg type with the actual name.  
>
> --
> Álvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>