主页 / dba / 问题 / 222064
Accepted
Sig
Asked: 2018-11-08 23:52:24 +0800 CST

将文本数组转换为 UUID 数组

  • 772

如何将texts 数组转换为 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_idsare not UUIDs 所以我尝试了：

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.

postgresql join

3 个回答

  1. Best Answer

    就像已经评论过的那样，该列project_ids应该是uuid[]，这将排除问题。也会更有效率。

    要更改（如您断言的列中没有非法数据）：

    ALTER TABLE users ALTER COLUMN project_ids DROP DEFAULT;

ALTER TABLE users
ALTER COLUMN project_ids SET DATA TYPE uuid[] USING project_ids::uuid[];

    你有uuid而不是uuid[]错误地。

    和这个：

    ERROR: default for column "product_ids" cannot be cast automatically to type uuid[]

    .. 表示您为该列设置了默认值。该表达式无法自动转换。在更改类型之前将其删除。您可以DEFAULT稍后添加新的。

    修复原始问题

    在原始情况下的有效解决方法是在强制转换之前从数组中删除空字符串（需要 Postgres 9.3+）：array_remove() 

    SELECT *
FROM   users    u
JOIN   projects p ON p.id = ANY(array_remove(u.project_ids, '')::uuid[]);

    ...在调查了为什么该列中可能有空刺之后text[]。

    有关的：

    细点

    • 查询中的会从结果[INNER] JOIN中删除没有有效项目的用户projects_ids。通常，您也希望保留这些：使用LEFT [OUTER] JOIN代替（users首先使用）。

    • 折叠以JOIN任何一种方式重复条目，这可能是也可能不是所希望的。如果要表示重复条目，请在加入之前取消嵌套。

    如果您的目标只是将 ID 数组解析为项目名称数组，您还需要保留数组元素的原始顺序：

    SELECT *
FROM   users u
LEFT   JOIN LATERAL (
   SELECT ARRAY(
      SELECT project_name  -- use the actual column(s) of your case
      FROM   unnest (array_remove(u.project_ids, '')::uuid[]) WITH ORDINALITY AS p(id, ord)
      JOIN   projects USING (id)
      ORDER  BY ord
      )
   ) p(projects) ON true;

    db<>fiddle here（大致基于McNets' fiddle）

    有关的：

    • 4 
  2. create table users (user_id int, projects text[]);

insert into users values
(1, (array['a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           'b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11'])::text[]);

insert into users values
(2, (array['a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           'b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11',
           'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'])::text[]);

insert into users values
(3, (array['f0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           '',
           'e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'])::text[]);

    create table projects (project_id uuid);

insert into projects values
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid),
('d0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid),
('e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);

    正如a_horse_with_no_name所指出的，如果某个数组元素没有值，它就会失败。它无法转换。

    select user_id, project_id
from   projects
join   users
on     project_id = any(projects::uuid[])

    错误：uuid 类型的输入语法无效：“”

    但是，您可以尝试以project_id这种方式转换为文本：

    select user_id, project_id
from   projects
join   users
on     project_id::text = any(projects);

    用户 ID | 项目编号                          
------: | :------------------------------------------------
      1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      3 | e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

    db<>在这里摆弄

    或者您可以扩展数组（未嵌套）并避免空/空值：

    with usr as
(
    select user_id, unnest(projects) as project_id
    from   users
)
select user_id, projects.project_id
from   projects
join   usr
on     coalesce(usr.project_id, '') <> ''
and    usr.project_id::uuid = projects.project_id;

    用户 ID | 项目编号                          
------: | :------------------------------------------------
      2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      3 | e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

    db<>在这里摆弄

    • 3

  3. 如何将文本数组转换为 UUID 数组？

    在你的情况下，我同意其他人的观点，这project_ids应该只是一个uuid[]开始。但是对于更一般的问题，您可以定义一个强制转换来自动处理这个问题：

    CREATE CAST (text[] AS uuid[]) WITH INOUT AS ASSIGNMENT;

    当您执行INSERT等操作时，这将自动为您转换内容。您也可以说AS IMPLICIT涵盖更多情况，尽管这可能会导致解析时的歧义。

    • 0

相关问题