將文本數組轉換為 UUID 數組

如何將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.

就像已經評論過的那樣，該列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[]。

有關的：

• 按索引刪除數組元素
• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎

細點

• 查詢中的會從結果[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）

有關的：

• 如何保留未嵌套數組中元素的原始順序？

引用自：https://dba.stackexchange.com/questions/222064