Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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)
share|improve this answer
add comment

If you have an array of institution_id, you can use unnest()

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

Using LEFT JOIN to the same effect.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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