时间戳

首页 > 解决方案 > 在 Postgres 中将字段添加和重命名为 ENUM 类型并保留要在字段中使用的多个 ENUM 类型

database - 在 Postgres 中将字段添加和重命名为 ENUM 类型并保留要在字段中使用的多个 ENUM 类型

问题描述

我正在使用 Postgres 和 postgres-migrations 来处理数据库迁移。

在第一个迁移脚本中，定义了基于以下 ENUM 标签的 TYPE。一个用户可以有多个角色。

CREATE TYPE user_role AS ENUM('PRIMARY', 'BILLING', 'CONTENT');

到目前为止，此类型已在 DB 中的两个表中使用，在分配的user_roles[]列角色数组中（这是一个示例）：

CREATE TABLE memberships (
id uuid DEFAULT uuid_generate_v1() PRIMARY KEY,
organisation_id uuid NOT NULL REFERENCES 
organisations(id),
roles user_role[] NOT NULL,
user_id uuid NOT NULL REFERENCES users(id)
);

我的目标是在以后的迁移脚本中添加另一个用户角色“ADMIN”，同时还希望将“CONTENT”重命名为“EDITOR”。

我最初尝试过，

ALTER TYPE user_role ADD VALUE 'ADMIN';

ALTER TYPE user_role RENAME VALUE 'CONTENT' TO 'EDITOR';

但是我遇到了以下错误：

ALTER TYPE ... ADD 不能在 runMigrations 的事务块内运行

在对网络上找到的解决方案进行了多次尝试之后，我得出了这个结论：

ALTER TYPE user_role RENAME TO user_role_old;

CREATE TYPE user_role AS ENUM('PRIMARY', 
'ADMIN', 'EDITOR', 'BILLING');

ALTER TABLE memberships ALTER COLUMN roles TYPE 
user_role USING roles::text::user_role;

ALTER TABLE organisation_invites ALTER COLUMN 
roles TYPE user_role USING 
roles::text::user_role;

DROP TYPE user_role_old;

这现在在类型中给出了正确的标签，但是这用 user_rolesuser_role替换了角色列类型。user_roles[]因此，我现在只能为一个不好的用户分配一个角色。我相信这与 cast 和 using 有关text，但我不确定。

标签： databasepostgresqlenums

解决方案

您应该始终使用数组。我很惊讶这些语句不会导致错误（所有行的列都是 NULL 吗？）。

ALTER TABLE memberships
   ALTER COLUMN role TYPE user_role
   USING roles::text[]::user_role[];

如您所见，一旦值发生变化，您就会在使用枚举类型时遇到麻烦。

我的建议是，如果值有可能改变或消失，则使用查找表。

推荐阅读