Return ALL results from an array even if that result is null

Question

I'm attempting to return:

  id | count | product | location
---------------------------------
 123 |    10 | acme 1  |        1
---------------------------------
 423 |  null |    null |     null
---------------------------------
8766 |     3 | wiley 1 |        1
---------------------------------
5232 |    15 | wiley 2 |        1
---------------------------------

you can view the previous question here where I was able to return the data except that it won't return id's of items which do not return by the filter. The assumption is that id 423 does exist in the array as the filter is location = 1 and this particular id has the location identifier of 4 within the table. But I still want it in the returned resultset

I'm using:

SELECT 
   t.id
   , count(t.id) total 
   , t.product
   , t.location
FROM unnest(Array[123,423,8766,5232]::integer[]) id
LEFT JOIN inventory t 
   ON t.id IS NOT DISTINCT 
FROM id.id
WHERE t.location_id = '1'
GROUP  BY 1,3,4;

but it will only return the items which are listed in the request, not the full array of id's which I would like to automatically return 0 or null

I'm using PostgreSQL 9.4.x

score 0 · Answer 1 · answered Dec 07 '15 at 12:39

0

Your query has two problems. First the where clause turns the outer join into an inner join. Second it counts t.id which can be null and count does not counts nulls. The fixed version:

select 
   t.id
   , count(*) total 
   , t.product
   , t.location
from
    unnest(array[123,423,8766,5232]::integer[]) id (id)
    left join
    inventory t on t.id = id.id and t.location_id = '1'
group  by 1,3,4;

answered Dec 07 '15 at 12:39

Clodoaldo Neto

118,695
26
233
260

your query still doesn't return anything different from what I'm already receiving. If the value (either) doesn't exist in the result set, or is null it isn't rendered. Instead i'm now receiving 1 null row for every variant array::integer[] test - so your answer isn't a fixed version, simply a different version of the same result – James Van Leuven Dec 11 '15 at 03:11
In order to properly adjust your query, I had to force a return of `id.id` and order by `id.id` including it in the group by (I simply replaced the `t.id` with `id.id`) also, i'm noticing the result set 'total' count for the null returns are not returning null or 0 for the count, but instead are returning `1` I suspect this is because a return exists – James Van Leuven Dec 11 '15 at 03:51

Return ALL results from an array even if that result is null

1 Answers1