PostgreSQL多列WHERE = ANY(ARRAY)

Question

我有一张包含三列的表格，分别是"id"、"letter"和"number"。我有一组"letter"和"number"的对应列表，需要在一次查询中获取对应的"id"。显然，最简单的解决方法是使用n个查询，其中n是列表的大小。

SELECT id FROM table WHERE number=... AND letter=...

但这需要n次查询，在我的情况下有数百万次查询，有很大的开销。以前，我只对“数字”列表进行了过滤，所以我使用了

SELECT id FROM table WHERE number = ANY(ARRAY[...])

请问是否有一些语法可以满足我的需求，类似于：

SELECT id FROM table WHERE PAIR[letter,number] = ANY(ARRAY[PAIR[...],...])

谢谢。

- Radek Svoboda

Answer 1

你可以使用一系列行记录：

select id from table where (letter, number) = any(array[(l1, n1), (l2, n2), ...])

如果你决定使用= any，你可能需要包含大量类型转换来确保所有内容都能对齐，这可能会比连接到一个VALUES列表更难看。

- mu is too short

---

这个并不起作用，然而问题可能是第二行实际上是“character varying”。我得到了ERROR: cannot compare dissimilar column types character varying and unknown at record column 2。 - Radek Svoboda

因此，在结尾处加上“您可能需要包括大量的类型转换以确保一切顺利”的注释。您可能需要这样说(l1 :: varchar，n1 :: int)才能获取正确的类型。 - mu is too short

Answer 2

虽然你可以使用数组和 <@ (is-contained-by) 运算符或 ANY 运算符，但这并不需要使用数组。与其他选项相比，固定值上的 JOIN 明显运行更快。

考虑下面带有数据的测试表：test。

CREATE TEMP TABLE IF NOT EXISTS 
    test(id SERIAL, letter TEXT, num NUMERIC);

WITH letters AS (
    SELECT chr(generate_series(65, 90)) AS letter
)
,numbers AS (
    SELECT generate_series(101, 999) AS num 
)
INSERT INTO test(letter, num)
SELECT  letter, num 
FROM    letters, numbers

现在可以将您的值添加到一个查询中，并执行JOIN操作。例如，下面的查询查找对('A', 105)，('B', 110)和('C',879)的id：

SELECT  id
FROM    test T
    JOIN (VALUES    /* your query criteria goes here */
        ('A', 105)
       ,('B', 110)
       ,('C', 879)
    ) AS V(l, n) 
        ON T.letter = V.l AND T.num = V.n 

假设之前没有写入到测试表中，此查询语句将返回：

id  |
----|
   5|
 909|
2577|

- isapir

---

谢谢，这个方法可行。我终于发现了 SELECT id FROM table WHERE (number, letter) = ANY (VALUES(1, 'A'), (2, 'B'), (3, 'C')) 这个看起来是最短的方法。 - Radek Svoboda