Postgres array lookup in WHERE clause

Question

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.

I'd like my query to return a row of [bar, NULL] instead, but can't think up a way to approach this.

Is this even possible?

Two things should clear up your query: `left outer join` will join as you request. Second, arrays are not really made to be searched through like mini tables. If you do not know the index of the array there is a good chance you are misusing them. — nate c, Mar 30 '11 at 23:55
I think the array is a red herring here. I believe he wants a row of (bar, NULL) returned, which makes sense in his context. — Peter Eisentraut, Mar 31 '11 at 04:26
Peter, array is just a way to accommodate multiple values of bar without having to generate a lot of 'bar = x or bar = y or...' clauses. I have no control over the bar values, it's my input and it can contain one or more values I have to query against. Hence the array. If there's more streamlined way to do this with SQL, I'd like to hear about it. — Alex Tokarev, Mar 31 '11 at 08:31
I think your question and my answer would be a better fit over on [dba.se](http://dba.stackexchange.com/) - if you agree, are you willing to consider self-flagging for migration there? — , Apr 27 '12 at 20:07

score 19 · Accepted Answer · 2011-03-31T13:46:54.750

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 unnest function, but you can roll your own (not very efficient) replacement:

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;

Jack, that's it. I have already found this wonderful unnest() function and even started to write an answer to my own question but you got there first. Thanks! :) — Alex Tokarev, Mar 31 '11 at 13:42
@Alexander You are very welcome - I've added an `unnest` function for anyone who is on <8.4 (not you of course as you've already said it works for you) — , Mar 31 '11 at 13:47

score 1 · Answer 2 · answered Mar 30 '11 at 20:01

1

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.

answered Mar 30 '11 at 20:01

Glen Solsberry

11,960
15
69
94

Try `LEFT JOIN` instead of `OUTER JOIN`. – Peter Eisentraut Mar 31 '11 at 04:27
Thanks for suggestion, but neither INNER JOIN nor LEFT JOIN does the trick. LEFT JOIN returns all rows where bar is defined, and I need to return a row for every requested value of bar, whether it exists in the foobar table or not. This is my problem. – Alex Tokarev Mar 31 '11 at 08:34

score 1 · Answer 3 · answered Mar 31 '11 at 13:42

1

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

answered Mar 31 '11 at 13:42

Quassnoi

413,100
91
616
614

Postgres array lookup in WHERE clause

3 Answers3