postgresql WHERE 子句中的 Postgres 数组查找
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5491586/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres array lookup in WHERE clause
提问by Alex Tokarev
I have a query:
我有一个疑问：
SELECT bar, (SELECT name FROM names WHERE value = bar) as name
FROM foobar WHERE foo = 1 and bar = ANY (1,2,3)
My problem is, when there is no row containing bar = 3(or whatever other value is requested) in table foobar, no rows are returned for that value of bar.
我的问题是，当bar = 3tablefoobar中没有包含（或请求的任何其他值）的行时，不会为 bar 的该值返回任何行。
I'd like my query to return a row of [bar, NULL]instead, but can't think up a way to approach this.
我希望我的查询返回一行[bar, NULL]，但想不出办法来解决这个问题。
Is this even possible?
这甚至可能吗？
回答by
Perhaps something like this approach is what you are after:
也许像这种方法是你所追求的：
testbed:
试验台：
create view names as
select 1 as value, 'Adam' as name union all select 2, 'Beth';
create view foobar as
select 1 as foo, 1 as bar union all select 1, 2;
original method:
原始方法：
select bar, (select name from names where value = bar) as name
from foobar
where foo = 1 and bar = any (array[1, 2, 3]);
bar | name
-----+------
1 | Adam
2 | Beth
(2 rows)
alternative method:
替代方法：
with w as (select unnest(array[1, 2, 3]) as bar)
select bar, (select name from names where value = bar) as name
from w left outer join foobar using(bar);
bar | name
-----+------
1 | Adam
2 | Beth
3 |
(3 rows)
If you are on 8.3 or before, there is no built-in unnestfunction, but you can roll your own (not very efficient) replacement:
如果您使用的是 8.3 或更早版本，则没有内置unnest函数，但您可以自行滚动（效率不高）替换：
create or replace function unnest(anyarray) returns setof anyelement as $$
select [i] from generate_series(array_lower(,1), array_upper(,1)) i;
$$ language 'sql' immutable;
回答by Glen Solsberry
SELECT bar, name
FROM foobar
INNER JOIN names ON foobar.bar = names.value
WHERE foo = 1 and bar = ANY (1,2,3)
Try that query instead.
改为尝试该查询。
回答by Quassnoi
SELECT vals.bar, name
FROM (
SELECT *
FROM unnest([1, 2, 3]) AS bar
) vals
LEFT JOIN
foobar
ON foobar.foo = 1
AND foobar.bar = vals.bar
LEFT JOIN
names
ON names.value = vals.bar
