Postgres Pro
Downloads
Home   >   mailing lists

Thread: ENUM drop label workaround

ENUM drop label workaround

From
Sergey Konoplev
Date:
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?

--
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@gmail.com

Re: ENUM drop label workaround

From
Arjen Nienhuis
Date:


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

Re: ENUM drop label workaround

From
Sergey Konoplev
Date:
On Fri, Sep 27, 2013 at 1:37 AM, Arjen Nienhuis <a.g.nienhuis@gmail.com> wrote:
>> 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.

We will have a lot of big tables with such fields and we couldn't
afford downtime on the ALTERs and other things.

--
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@gmail.com