Обсуждение: enum bug
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
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.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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.