我有一个使用枚举类型的表列。我希望更新枚举类型有一个额外的可能值。我不想删除任何现有的值,只是添加新的值。最简单的方法是什么?


当前回答

当使用Navicat时,您可以转到类型(在视图下-> others -> types) -获得类型的设计视图-然后单击“添加标签”按钮。

其他回答

更新pg_enum可以工作,就像上面突出显示的中间列技巧一样。你也可以使用USING魔法直接改变列的类型:

CREATE TYPE test AS enum('a', 'b');
CREATE TABLE foo (bar test);
INSERT INTO foo VALUES ('a'), ('b');

ALTER TABLE foo ALTER COLUMN bar TYPE varchar;

DROP TYPE test;
CREATE TYPE test as enum('a', 'b', 'c');

ALTER TABLE foo ALTER COLUMN bar TYPE test
USING CASE
WHEN bar = ANY (enum_range(null::test)::varchar[])
THEN bar::test
WHEN bar = ANY ('{convert, these, values}'::varchar[])
THEN 'c'::test
ELSE NULL
END;

只要没有函数显式地要求或返回该枚举,就没问题。(如果有,pgsql会在删除类型时报错。)

另外,请注意PG9.1引入了一个ALTER TYPE语句,它将对枚举起作用:

http://developer.postgresql.org/pgdocs/postgres/release-9-1-alpha.html

下面是一个更通用但工作相当快的解决方案,除了更改类型本身之外,还使用它更新数据库中的所有列。即使ENUM的新版本不同于多个标签或遗漏了一些原始标签,也可以应用该方法。下面的代码替换my_schema。my_type ENUM (a, b, c)与枚举(“a”、“b”,“d”,“e”):

CREATE OR REPLACE FUNCTION tmp() RETURNS BOOLEAN AS
$BODY$

DECLARE
    item RECORD;

BEGIN

    -- 1. create new type in replacement to my_type
    CREATE TYPE my_schema.my_type_NEW
        AS ENUM ('a', 'b', 'd', 'e');

    -- 2. select all columns in the db that have type my_type
    FOR item IN
        SELECT table_schema, table_name, column_name, udt_schema, udt_name
            FROM information_schema.columns
            WHERE
                udt_schema   = 'my_schema'
            AND udt_name     = 'my_type'
    LOOP
        -- 3. Change the type of every column using my_type to my_type_NEW
        EXECUTE
            ' ALTER TABLE ' || item.table_schema || '.' || item.table_name
         || ' ALTER COLUMN ' || item.column_name
         || ' TYPE my_schema.my_type_NEW'
         || ' USING ' || item.column_name || '::text::my_schema.my_type_NEW;';
    END LOOP;

    -- 4. Delete an old version of the type
    DROP TYPE my_schema.my_type;

    -- 5. Remove _NEW suffix from the new type
    ALTER TYPE my_schema.my_type_NEW
        RENAME TO my_type;

    RETURN true;

END
$BODY$
LANGUAGE 'plpgsql';

SELECT * FROM tmp();
DROP FUNCTION tmp();

整个过程将运行得相当快,因为如果标签的顺序保持不变,则不会发生实际的数据更改。我使用my_type在5个表上应用了这个方法,每个表中有50,000 - 70,000行,整个过程只花了10秒。

当然,如果在数据的某个地方使用了新版本ENUM中缺少的标签,则该函数将返回一个异常,但在这种情况下,无论如何都应该事先做一些事情。

来自Postgres 9.1文档:

ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]

例子:

ALTER TYPE user_status ADD VALUE 'PROVISIONAL' AFTER 'NORMAL'

PostgreSQL 9.1引入了ALTER Enum类型的功能:

ALTER TYPE enum_type ADD VALUE 'new_value'; -- appends to list
ALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'old_value';
ALTER TYPE enum_type ADD VALUE 'new_value' AFTER 'old_value';

不能添加注释到适当的位置,但ALTER TABLE foo ALTER COLUMN bar TYPE new_enum_type USING bar::text::new_enum_type默认列失败。我不得不:

ALTER COLUMN bar DROP DEFAULT

然后就成功了。