12

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?

2 Answers 2

19

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)
5

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.