如何匹配復合型別陣列中的元素？

假設我們有兩個表：

CREATE TABLE element (
    pk1 BIGINT NOT NULL,
    pk2 BIGINT NOT NULL,
    pk3 BIGINT NOT NULL,
    -- other columns ...
    PRIMARY KEY (pk1, pk2, pk3)
);

CREATE TYPE element_pk_t AS (
    pk1 BIGINT,
    pk2 BIGINT,
    pk3 BIGINT
);

CREATE TABLE collection (
    id BIGINT,
    elements element_pk_t[] NOT NULL,
);

該element具有復合PK。自定義型別element_pk_t注冊匹配的復合型別。該collection表包含element_pk_t.

我想在單個查詢中查詢表element中 PK 與 selected 中的元素匹配的所有行collection.elements。

我試過的：

SELECT * 
FROM element 
WHERE (pk1, pk2, pk3) IN (SELECT unnest(elements) 
                          FROM collection 
                          WHERE id = 1);

我在IN子句中遇到錯誤：

錯誤：子查詢的列太少

但是，這有效：

SELECT * 
FROM element 
WHERE (pk1, pk2, pk3) IN ((1, 2, 3), (4, 5, 6));

所以看起來問題是如何將自定義型別擴展element_pk_t到可以匹配的3列(pk1, pk2, pk3)。

uj5u.com熱心網友回復：

這有效：

SELECT *
FROM   element 
WHERE  (pk1, pk2, pk3) IN (SELECT (unnest(elements)).*
                           FROM   collection
                           WHERE  id = 1);

或者更詳細，但更可取：

SELECT *
FROM   element 
WHERE  (pk1, pk2, pk3) IN (SELECT (e).*
                           FROM   collection c, unnest(c.elements) e
                           WHERE  c.id = 1);

更健壯，避免unnest()多次評估。看：

• JOIN on set 回傳函式結果

這也有效：

SELECT *
FROM   element 
WHERE  ROW((pk1, pk2, pk3)) IN (SELECT unnest(elements)
                                FROM   collection
                                WHERE  id = 1);

問題的核心是IN采用子查詢知道兩種不同的形式。參考手冊：

expression IN (subquery)

row_constructor IN (subquery)

您失敗的查詢決議為第二種形式，而您（可以理解）期望第一種形式。但是第二種形式是這樣做的：

這種形式的左側IN是一個行建構式，如第 4.2.13 節所述。右側是一個帶括號的子查詢，它必須回傳與左側行中的運算式一樣多的列。左邊的運算式被評估并與子查詢結果的每一行逐行比較。[...]

我的第一個和第二個查詢通過分解運算子右側的行型別來使其作業。所以Postgres有bigintleft和right三個值，滿足。

我的第三個查詢通過將行型別嵌套到另一個行建構式的左側來使其作業。Postgres 只分解第一層并最終得到一個單一的復合型別 - 將單一復合型別與右側匹配。

請注意，ROW我們包裝的單個欄位需要關鍵字。手冊：

ROW當串列中有多個運算式時，關鍵字是可選的。

Your working query is subtly different as it provides a list of values to the right instead of a subquery (set). That's a different implementation taking a different code path. It even gets a separate chapter in the manual. This variant has no special treatment for a ROW constructor to the left. So it just works as expected (by you).

More equivalent (working) syntax variants with = ANY:

SELECT * FROM element 
WHERE (pk1, pk2, pk3) = ANY ('{"(1,2,3)","(2,3,4)"}'::element_pk_t[]);

SELECT * FROM element 
WHERE (pk1, pk2, pk3) = ANY (ARRAY[(1,2,3)::element_pk_t,(2,3,4)::element_pk_t]);

SELECT * FROM element 
WHERE (pk1, pk2, pk3) = ANY (ARRAY[(1,2,3),(2,3,4)]::element[]);

Also valid with (pk1, pk2, pk3)::element_pk_t or ROW(pk1, pk2, pk3)::element_pk_t

See:

• IN vs ANY operator in PostgreSQL

由于您的源是一個array，丹尼爾的第二個查詢很 (e.pk1, e.pk2, e.pk3) = ANY(c.elements)自然。
但是對于最快查詢的賭注，我的錢是在我的第二個變體上，因為我希望它能夠最佳地使用 PK 索引。

就像概念證明一樣。就像 a_horse 評論的那樣：標準化的資料庫設計可能會最好地擴展。

uj5u.com熱心網友回復：

您需要進行額外級別的解包，以便使用 IN 子句使其作業。需要從子查詢回傳一組常規欄位以與外部行進行比較。

SELECT *
FROM element
WHERE row(pk1, pk2, pk3) IN (
  SELECT (x.el).pk1, (x.el).pk2, (x.el).pk3
  FROM collection
  CROSS JOIN LATERAL (SELECT unnest(elements) el FROM collection) x
  WHERE id = 1
)

清潔器將通過檢查元素是否在連接子句中的陣列中來連接。

SELECT e.*
FROM element e
INNER JOIN collection c ON row(e.pk1, e.pk2, e.pk3) = ANY(c.elements)
   AND c.id = 1

標籤：sql PostgreSQL sql输入 行类型

上一篇：當值為零時獲取開始和結束日期時間

下一篇：Postgresql->JSON陣列到行