如何将text
s 数组转换为 s 数组UUID
?
我需要join
在两个表之间做一个:users
和projects
.
该users
表有一个名为的数组字段project_ids
,其中包含项目 ID 作为文本。
该projects
表有一个名为 的 UUID 字段id
。
我最初的想法是查询如下:
SELECT * FROM projects
JOIN users ON
projects.id = ANY(users.project_ids)
但这不起作用,因为users.project_ids
are not UUID
s 所以我尝试了:
projects.id = ANY(users.project_ids::uuid[])
乃至:
projects.id = ANY(ARRAY[users.project_ids]::uuid[])
但没有一个工作:
ERROR: invalid input syntax for type uuid: ""
更新
@a_horse_with_no_name 绝对正确。最好的选择应该是使用一组 UUID。
现在的问题是如何将数组更改为text
数组uuid
?
该users
表当前为空(0 条记录)。
我努力了
ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID USING "project_ids"::uuid[];
这会产生
错误:列“product_ids”的 USING 子句的结果无法自动转换为类型 uuid 提示:您可能需要添加显式转换。
ALTER TABLE "users" ALTER COLUMN "product_ids" 使用 "product_ids"::UUID 设置数据类型 UUID;
我也试过
ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID[] USING "project_ids"::uuid[];
这会产生
错误:列“project_ids”的默认值不能自动转换为类型 uuid[]
该列默认设置为空数组。
我正在运行 PG 版本 10.4,project_ids
目前是text[] nullable
.
就像已经评论过的那样,该列
project_ids
应该是uuid[]
,这将排除问题。也会更有效率。要更改(如您断言的列中没有非法数据):
你有
uuid
而不是uuid[]
错误地。和这个:
.. 表示您为该列设置了默认值。该表达式无法自动转换。在更改类型之前将其删除。您可以
DEFAULT
稍后添加新的。修复原始问题
在原始情况下的有效解决方法是在强制转换之前从数组中删除空字符串(需要 Postgres 9.3+):
array_remove()
...在调查了为什么该列中可能有空刺之后
text[]
。有关的:
细点
查询中的会从结果
[INNER] JOIN
中删除没有有效项目的用户projects_ids
。通常,您也希望保留这些:使用LEFT [OUTER] JOIN
代替(users
首先使用)。折叠以
JOIN
任何一种方式重复条目,这可能是也可能不是所希望的。如果要表示重复条目,请在加入之前取消嵌套。如果您的目标只是将 ID 数组解析为项目名称数组,您还需要保留数组元素的原始顺序:
db<>fiddle here(大致基于McNets' fiddle)
有关的:
正如a_horse_with_no_name所指出的,如果某个数组元素没有值,它就会失败。它无法转换。
但是,您可以尝试以
project_id
这种方式转换为文本:db<>在这里摆弄
或者您可以扩展数组(未嵌套)并避免空/空值:
db<>在这里摆弄
在你的情况下,我同意其他人的观点,这
project_ids
应该只是一个uuid[]
开始。但是对于更一般的问题,您可以定义一个强制转换来自动处理这个问题:CREATE CAST (text[] AS uuid[]) WITH INOUT AS ASSIGNMENT;
当您执行
INSERT
等操作时,这将自动为您转换内容。您也可以说AS IMPLICIT
涵盖更多情况,尽管这可能会导致解析时的歧义。