我有一个使用枚举类型的表列。我希望更新枚举类型有一个额外的可能值。我不想删除任何现有的值,只是添加新的值。最简单的方法是什么?
当前回答
如上所述,ALTER命令不能写入事务内部。建议的方法是直接插入到pg_enum表中,从pg_type表中检索typelem并计算下一个enumsortorder号;
以下是我使用的代码。检查插入前是否存在重复值(enumtypid和enumlabel名称之间的约束)
INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
SELECT typelem,
'NEW_ENUM_VALUE',
(SELECT MAX(enumsortorder) + 1
FROM pg_enum e
JOIN pg_type p
ON p.typelem = e.enumtypid
WHERE p.typname = '_mytypename'
)
FROM pg_type p
WHERE p.typname = '_mytypename'
AND NOT EXISTS (
SELECT * FROM
pg_enum e
JOIN pg_type p
ON p.typelem = e.enumtypid
WHERE e.enumlabel = 'NEW_ENUM_VALUE'
AND p.typname = '_mytypename'
)
注意,在pg_type表中,类型名前面有下划线。此外,where子句中的typname必须全部小写。
现在这可以安全地写入您的db migrate脚本。
其他回答
来自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'
DB::statement("ALTER TABLE users DROP CONSTRAINT users_user_type_check");
$types = ['old_type1', 'old_type1', 'new_type3'];
$result = join( ', ', array_map(function ($value){
return sprintf("'%s'::character varying", $value);
}, $types));
DB::statement("ALTER TABLE users ADD CONSTRAINT users_user_type_check CHECK (user_type::text = ANY (ARRAY[$result]::text[]))");
如果你正在使用Postgres 12(或更高版本),你可以运行ALTER TYPE…在交易(文档)中增加价值。
如果ALTER TYPE…ADD VALUE(向枚举中添加新值的表单 类型)在事务块内执行,则新值不能为 一直使用到事务提交之后。
因此在迁移中不需要任何hack。
UPD:这里有一个例子(感谢Nick)
添加一个新值
当使用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
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- MySQL数据库表中的最大记录数
- 我可以在Java中设置enum起始值吗?
- 从现有模式生成表关系图(SQL Server)
- HyperLogLog算法是如何工作的?
- 纬度和经度的数据类型是什么?
- 数据库和模式的区别
- 如何在PostgreSQL中临时禁用触发器?
- c# vs Java Enum(适合c#新手)
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 如何从命令行在windows中找到mysql数据目录
- 货币应该使用哪种数据类型?