Re: ENUM drop label workaround

On Sep 26, 2013 9:20 PM, "Sergey Konoplev" <gray(dot)ru(at)gmail(dot)com> wrote:
>
> Hi,
>
> AIU we have no ability to drop a label from ENUM currently, and there
> are no plans to add this feature in the nearest future.
>
> I came to a workaround via DOMAIN, here it is:
>
> CREATE TYPE ref AS ENUM ('aaa', 'bbb');
>
> CREATE DOMAIN refdom AS ref DEFAULT 'aaa';
>
> ALTER TABLE table1 ADD refdom_column refdom;
>
> ALTER DOMAIN refdom ADD CONSTRAINT refdom_deleted_chk
> CHECK (VALUE NOT IN ('bbb'));
>
> UPDATE table1 SET refdom_column = 'bbb';
> ERROR: value for domain refdom violates check constraint
"refdom_deleted_chk"
>
> UPDATE table1 SET refdom_column = 'ccc';
> ERROR: invalid input value for enum ref: "ccc"
>
> Are there any caveats of this solution and may be there is a better one?
>

Why don't you just create a new ENUM with fewer values? You can use:

ALTER TABLE ... SET DATA TYPE ... USING col1::text::new_enum_type;

You might need to recreate some foreign keys.

> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
>
> http://www.linkedin.com/in/grayhemp
> +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
> gray(dot)ru(at)gmail(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general