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?

Daniel Vérité · Accepted Answer · 2013-03-26 19:35:51Z

20

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)

answered Mar 26, 2013 at 19:35

Daniel Vérité

32.9k3 gold badges78 silver badges84 bronze badges

Add a comment |

Erwin Brandstetter · Accepted Answer · 2022-12-15 11:39:43Z

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

Stack Exchange Network

Postgres RIGHT JOIN with custom array

2 Answers 2

Hot Network Questions

Postgres RIGHT JOIN with custom array

2 Answers 2

Related

Hot Network Questions