October 27th, 2010 by depesz | Tags: , , , , , | 9 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

Finally, on 25th of October, Tom Lane committed patch which does:

Allow new values to be added to an existing enum type.
 
After much expenditure of effort, we've got this to the point where the
performance penalty is pretty minimal in typical cases.
 
Andrew Dunstan, reviewed by Brendan Jurd, Dean Rasheed, and Tom Lane

As you perhaps know ENUM datatype was added to PostgreSQL in 8.3, but (at the very least for me) it was virtually unusable, as it couldn't be modified.

Well, truth be told – it could be modified by changing catalogs, but I just don't feel smart enough to modify catalogs with normal queries, so I just don't do it.

Now – we got a way to add new values to ENUMS.

Let's see how it works. First I will need some enum:

CREATE TYPE mood AS ENUM (
    'sad', 'ok', 'happy'
);

And now I can use the values with casts to enum:

select 'sad'::mood;
mood
------
sad
(1 row)

This example is not really helpful, but I wanted to show as simple as possible something that works, and something that doesn't:

select 'dead'::mood;
ERROR: invalid input value for enum mood: "dead"
LINE 1: select 'dead'::mood;
^

While is might be debated whether dead is a mood, the fact is that our enum doesn't allow it. But if we'd really need, we can now:

alter type mood add 'dead' BEFORE 'happy';

And now the datatype looks like:

\dT+ mood
List of data types
Schema | Name | Internal name | Size | Elements | Description
--------+------+---------------+------+----------+-------------
public | mood | mood | 4 | sad +|
| | | | ok +|
| | | | dead +|
| | | | happy |
(1 row)

And so, I can:

select 'dead'::mood;
mood
------
dead
(1 row)

It's great. ENUM still doesn't solve all needs (what if we no longer support users which are dead? There is no way to remove the value from ENUM, but it's a big step towards making ENUMs fully useful.

  1. 9 comments

  2. # gregj
    Oct 27, 2010

    so we will get remove (drop) of enum values in (9.1-8.3) 3 releases from 9.1, so 9.4 :)

    nice ;)

  3. Oct 27, 2010

    It is worth noting that Andrew Dunstan proposed to use:

    ALTER TYPE enumtype ADD LABEL ‘newlabel’

    And I completely agree with him, just like some other guys.

  4. Oct 27, 2010

    These two functions were borrowed from another web page (can’t remember where, but I’m sure a google search will find the original author) and modified to take schema’s into account:

    CREATE OR REPLACE FUNCTION enum_add(enum_name character varying, enum_elem character varying, enum_schema character varying DEFAULT 'public'::character varying)
      RETURNS void AS
    $BODY$
    DECLARE
        schemaoid       integer;
    BEGIN
        SELECT oid INTO schemaoid FROM pg_namespace WHERE nspname = enum_schema;
        IF NOT FOUND THEN
        RAISE EXCEPTION 'Could not find schema ''%''', enum_schema;
        END IF;
        INSERT INTO pg_enum(enumtypid, enumlabel) VALUES(
            (SELECT oid FROM pg_type WHERE typtype='e' AND typname=enum_name AND typnamespace = schemaoid),
            enum_elem
        );
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    CREATE OR REPLACE FUNCTION enum_del(enum_name character varying, enum_elem character varying, enum_schema character varying DEFAULT 'public'::character varying)
      RETURNS void AS
    $BODY$
    DECLARE
        type_oid INTEGER;
        rec RECORD;
        sql VARCHAR;
        ret INTEGER;
        schemaoid INTEGER;
    BEGIN
        SELECT oid INTO schemaoid FROM pg_namespace WHERE nspname = enum_schema;
        IF NOT FOUND THEN
        RAISE EXCEPTION 'Could not find schema ''%''', enum_schema;
        END IF;
        SELECT pg_type.oid
        FROM pg_type
        WHERE typtype = 'e' AND typname = enum_name AND typnamespace = schemaoid
        INTO type_oid;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Cannot find a enum: %', enum_name;
        END IF;
        -- Check column DEFAULT value references.
        SELECT *
        FROM
            pg_attrdef
            JOIN pg_attribute ON attnum = adnum AND atttypid = type_oid
            JOIN pg_class ON pg_class.oid = attrelid
            JOIN pg_namespace ON pg_namespace.oid = relnamespace
        WHERE
            adsrc = quote_literal(enum_elem) || '::' || quote_ident(enum_name)
        LIMIT 1
        INTO rec;
        IF FOUND THEN
            RAISE EXCEPTION
                'Cannot delete the ENUM element %.%: column %.%.% has DEFAULT value of ''%''',
                quote_ident(enum_name), quote_ident(enum_elem),
                quote_ident(rec.nspname), quote_ident(rec.relname),
                rec.attname, quote_ident(enum_elem);
        END IF;
        -- Check data references.
        FOR rec IN
            SELECT *
            FROM
                pg_attribute
                JOIN pg_class ON pg_class.oid = attrelid
                JOIN pg_namespace ON pg_namespace.oid = relnamespace
            WHERE
                atttypid = type_oid
                AND relkind = 'r'
        LOOP
            sql :=
                'SELECT 1 FROM ONLY '
                || quote_ident(rec.nspname) || '.'
                || quote_ident(rec.relname) || ' '
                || ' WHERE '
                || quote_ident(rec.attname) || ' = '
                || quote_literal(enum_elem)
                || ' LIMIT 1';
            EXECUTE sql INTO ret;
            IF ret IS NOT NULL THEN
                RAISE EXCEPTION
                    'Cannot delete the ENUM element %.%: column %.%.% contains references',
                    quote_ident(enum_name), quote_ident(enum_elem),
                    quote_ident(rec.nspname), quote_ident(rec.relname),
                    rec.attname;
            END IF;
        END LOOP;
        -- OK. We may delete.
        DELETE FROM pg_enum WHERE enumtypid = type_oid AND enumlabel = enum_elem;
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

    Both functions should work in 8.3, 8.4 and 9.0. Usage would be as follows:

    SELECT enum_add('enumtype', 'label', 'my_schema');
    SELECT enum_del('enumtype', 'label', 'my_schema');

    The only big problem I’ve run into is that calling enum_del to remove a value that’s still used in the database can cause all kinds of issues particularly in function definitions. At some point I’d like to modify the enum_del function to check the entire database to see if the value you wish to remove is in use anywhere, but I haven’t had time to research this to see if it’s possible. I’d also worry that it may be too performance intensive to perform this check automatically.

  5. Oct 27, 2010

    Apologies about the formatting on that last post – if you are able please could you tidy up the new lines? Thanks!

  6. Oct 28, 2010

    I wrote post with poll about syntax of this feature: http://pgolub.wordpress.com/2010/10/28/poll-alter-type-enumtype-add-what-newlabel/

  7. # Andrew Dunstan
    Oct 28, 2010

    The functions above should not be used for several reasons. The add function does not cope with Oid wraparound, and the delete function is seriously incomplete. In general you should try hard to avoid manually mangling the catalogs. If you care enough about it enough, contribute a patch rather than hacking the system tables like this.

    Note that the new 9.1 functionality is guaranteed to preserve the sort order of the existing elements and allows you to add the new element at any arbitrary place in the sort order. That’s something that’s quite impossible with 9.0 and previous, since the catalog didn’t have any support for explicit sort order (the Oids defined the sort order).

  8. # szymon
    Dec 30, 2011

    its limited:

    ‘ALTER TYPE … ADD cannot be executed from a function or multi-command string’

    :(

  9. # Gyorgy Abraham
    May 17, 2012

    Also:

    “ALTER TYPE … ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.”

    We got serious work while being on 9.0 with enums, 9.1 won’t really help us because of this limitation.

  10. # asghar
    Aug 26, 2012

    SOLVED !

    use this syntax :
    alter type cve_severity ADD VALUE ‘aaa’;

Leave a comment