arrays - WHERE 子句中的 Postgres 数组查找

Question

我有一个问题:

SELECT bar, (SELECT name FROM names WHERE value = bar) as name
FROM foobar WHERE foo = 1 and bar = ANY (1,2,3)

我的问题是，当 foobar 表中没有包含 bar = 3(或请求的任何其他值)的行时，不会为该值返回任何行酒吧。

我希望我的查询返回一行 [bar, NULL]，但想不出解决这个问题的方法。

这可能吗？

Answer 1

也许这种方法就是您所追求的:

测试平台:

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;

原始方法:

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)

替代方法:

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)

如果您使用的是 8.3 或更早版本，则没有内置的 unnest 函数，但您可以自己滚动(不是很有效)替换:

create or replace function unnest(anyarray) returns setof anyelement as $$
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$$ language 'sql' immutable;