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

Question

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

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

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

到目前为止，此类型已在分配的 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 cannot run inside a transaction block at 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_role 类型中给出了正确的标签，但是这用 user_roles 替换了 user_roles[] 的角色列类型。因此，我现在只能将一个角色分配给一个不好的用户。我相信这与转换和使用 text 有关，但我不确定。

Answer 1

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

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

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

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