代码之家 › 专栏 › 技术社区 › Shorn

Postgres-如何将enum\u 1的数组强制转换为enum\u 2?

postgresql sql

Shorn · 技术社区 · 6 年前

我正在尝试修改架构中枚举的值(“ feature “在下面的示例中)。

我试图通过重命名旧枚举并引入一个具有所需值的新枚举,然后将表定义更改为新枚举来实现这一点。

我在这里关注这篇博文: https://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/ . 但我的列不是枚举的简单列,而是枚举的数组。

当我尝试运行 alter table 语句在下面的语句中,我得到了错误:

[42804]错误:“features”列的类型为feature\u old[],但表达式的类型为feature\u v2[]。提示:您需要重写或强制转换表达式。

alter type feature rename to feature_old;

create type feature_v2 as enum (
  'enable_create_keyword',
  'enable_make_payment',
  'enable_test_data_flags'
);

-- ... cleanup of column array values to be compatible with new enum ...

alter table app_user alter column features type feature_v2
using features::feature_old[]::feature_v2[];

drop type feature_old;

但是,我迷路了——演员的表情应该是什么样的?

Postgres版本为9.6

编辑

这是以前版本的架构DDL的相关部分 特色 枚举和 app_user @VaoTsun请求的表。

-- feature enum and column

create type feature as enum ('enable_create_keyword', 'enable_make_payment');
comment on type feature is 
  'if default functionality is disabled feature name starts with enable_, if default is enabled starts with disable_'
;

alter table app_user add column 
features feature[] not null default ARRAY[]::feature[];


-- feature data
update app_user
set features = ARRAY['enable_create_keyword', 'enable_make_payment']::feature[]
where email = 'test1@example.com';

update app_user
set features = ARRAY['enable_create_keyword']::feature[]
where email = 'test2@example.com';

2 回复 | 直到 6 年前

Shorn 4 年前

感谢沃森和尼克·巴恩斯;这是一个似乎对我有用的代码。我已将Vao Tsun的回答标记为正确。任何能提供更简洁版本的答案都将被恭维地更新。

alter type feature rename to feature_old;

create type feature_v2 as enum (
  'enable_create_keyword',
  'enable_make_payment',
  'enable_test_data_flags'
);

alter table app_user alter column features drop default ;

alter table app_user alter column features type feature_v2[]
using features::feature_old[]::text[]::feature_v2[];

alter table app_user alter column features set default ARRAY[]::feature_v2[];

drop type feature_old;

Vao Tsun 6 年前

假设旧枚举的值相同,但较少,您应该能够简单地将其值转换为文本,然后转换为v2:

尝试以下操作:

t=# create or replace function feature2v2(feature_old) returns feature_v2 as
$$
select $1::text::feature_v2;
$$
language sql strict;
CREATE FUNCTION
t=# create cast (feature_old AS feature_v2) WITH FUNCTION feature2v2(feature_old) AS ASSIGNMENT;
CREATE CAST

给我:

t=# alter table app_user alter column features type feature_v2[]
using features::feature_v2[];
ALTER TABLE
t=# \d+ app_user
                                            Table "postgres.app_user"
  Column  |     Type     | Collation | Nullable |        Default         | Storage  | Stats target | Description
----------+--------------+-----------+----------+------------------------+----------+--------------+-------------
 email    | text         |           |          |                        | extended |              |
 features | feature_v2[] |           | not null | ARRAY[]::feature_old[] | extended |              |

t=# \dT+ feature_v2
                                                 List of data types
  Schema  |    Name    | Internal name | Size |        Elements        |  Owner   | Access privileges | Description
----------+------------+---------------+------+------------------------+----------+-------------------+-------------
 postgres | feature_v2 | feature_v2    | 4    | enable_create_keyword +| postgres |                   |
          |            |               |      | enable_make_payment   +|          |                   |
          |            |               |      | enable_test_data_flags |          |                   |
(1 row)

看起来就像你所期望的

更新

跟上Nick Barnes的评论-在这里创建演员阵容是一项开销,给专栏留下了不好的定义,她的正确做法是:

alter table app_user alter column features drop default;
alter table app_user alter column features type feature_v2[] using features::feature_old[]::text[]::feature_v2[];
alter table app_user alter column features set default ARRAY[]::feature_v2[];

保持前一个版本不变,以演示糟糕的方法,并提示其糟糕之处