On Sep 26, 2013 9:20 PM, "Sergey Konoplev" <gray.ru@xxxxxxxxx> 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.ru@xxxxxxxxx
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
