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 unnecessary 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?

Answer 1

You may do an OUTER JOIN directly with a list of values, like this:

SELECT v.id,tablename.* FROM tablename RIGHT JOIN (values (1),(3),...,(9)) as v(id)
 ON (tablename.field=v.id)

Answer 2

Based on an array of IDs, you can use unnest():

SELECT i.institution_id, t.*
FROM unnest('{1, 3, 4, 5, 7, 9}'::int[]) institution_id
LEFT JOIN tbl t USING (institution_id);

Using LEFT JOIN to the same effect.
Or just:

SELECT *
FROM unnest('{1, 3, 4, 5, 7, 9}'::int[]) institution_id
LEFT JOIN tbl t USING (institution_id);

The USING clause in the join condition only adds a single instance of institution_id to the output columns. SELECT * may be exactly what you want. Or not. If all other columns of tbl can be NULL (institution_id being the only not-null column), you can't tell the difference now between a missing row and a row of NULL values.

To also preserve the order of elements in the input array:

SELECT *
FROM unnest('{7, 3, 4, 1, 5, 9}'::int[]) WITH ORDINALITY AS i(institution_id, ord)
LEFT JOIN tbl t USING (institution_id);
ORDER BY i.ord;

See:

• PostgreSQL unnest() with element number