将varchar[ ]列类型转换为uuid[ ]

Question

有一个字符类型为varying[]的列，该列包含uuid类型的数据，如{0f071799-e37e-4e1c-9620-e580447416fe,913a7134-6092-45fa-ae18-163302db8112}，

但也有其他类型的旧值，如{5edfd4edfa1bb21a142442a0}。

如何转换列类型？我使用了脚本：

alter table services alter column office_ids type uuid[] USING office_ids::uuid[];

但是给出了uuid类型的错误无效语法：“5edfd4edfa1bb21a14244a0”。

Answer 1

您必须首先将25个字符值转换为有效的uuid值。

其中之一是：

8f5f7cc46821423fa6057025a -> 00000008-f5f7-cc46-8214-23fa6057025a

用于此的SQL是：

regexp_replace('8f5f7cc46821423fa6057025a', '^(.)(.{4})(.{4})(.{4})(.{12})^', '0000000\1-\2-\3-\4-\5')

产出：

00000008-f5f7-cc46-8214-23fa6057025a

这使得有效的uuid保持不变。见现场演示。

您可以使用它来更新下面这样的坏值：

update services set office_ids = array(
  select regexp_replace(t.val, '^(.)(.{4})(.{4})(.{4})(.{12})$', '0000000\1-\2-\3-\4-\5')
  from unnest(services.office_ids) as t(val)
)

那么你的alter命令就能工作了。

见现场演示。