Waiting for 9.1 – adding values to enums

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.

9 thoughts on “Waiting for 9.1 – adding values to enums”

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

    nice 😉

  2. 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.

  3. 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.

  6. 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).

  7. its limited:

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

    🙁

  8. 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.

  9. SOLVED !

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

Comments are closed.