Postgres RIGHT JOIN with custom array

Question

I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following:

SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)

The ... is not important to this question, it's just important that it returns a result with the institution_ids in the array (1, 3, 4, 5, 7, 9) and it includes those institutions with no data. Here is an example of the current output

days    treatments    institution_id
266    6996    4
265    5310    1
267    3361    5
260    2809    3
264    5249    7

An example of the output I want is

days    treatments    institution_id
266    6996    4
265    5310    1
267    3361    5
260    2809    3
264    5249    7
               9

I know I can achieve this by using the following query

SELECT * FROM (SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)) RIGHT JOIN generate_series(1,9) ON generate_series = institution_id WHERE generate_series IN (1, 3, 4, 5, 7, 9)

However, this is extra work because generate_series(1,9) creates institution_ids I'm not interested in, it requires that I know the max institution_id a priori, and it introduces an uncessary WHERE clause. Ideally I'd like a query like the following

SELECT * FROM (SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)) RIGHT JOIN (1, 3, 4, 5, 7, 9) ON generate_series = institution_id

Where (1, 3, 4, 5, 7, 9) is just an array that Postgres will use for the JOIN command. I've also already tried [1, 3, 4, 5, 7, 9] and {1, 3, 4, 5, 7, 9} both to no avail.

Any ideas?

Thanks

Clodoaldo Neto · Accepted Answer · 2016-07-20 08:34:38Z

4

select i.days, i.treatments, s.id institution_id
from
    institution i
    right join (
        values (1), (3), (4), (5), (7), (9)
    ) s (id) on i.institution_id = s.id

Or

select i.days, i.treatments, s.id institution_id
from
    institution i
    right join
    unnest(array[1, 3, 4, 5, 7, 9]) s (id) on i.institution_id = s.id

edited Jul 20, 2016 at 8:34

answered Mar 26, 2013 at 17:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Over a year ago

Thanks, I'm going to go with the second solution (looks simpler). This is exactly what I was looking for.

Collectives™ on Stack Overflow

Postgres RIGHT JOIN with custom array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related