Postgresql – Postgres RIGHT JOIN with custom array

join;postgresql

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?

Best Answer

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)

Related Solutions

Postgresql – Combine a generated series with each row from a select

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report.

Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query.

Here's a simplified example to show you how it works:

CREATE TABLE sparse_dates(
    bakedgood text primary key,
    firstbaked date not null
);

INSERT INTO sparse_dates (bakedgood, firstbaked) VALUES
('tart', '2012-02-01'),
('baguette', '2012-02-01'),
('cookie', '2012-03-01'),
('macaron', '2012-08-01');

To get the number of baked goods first baked on a given month:

SELECT monthtimestamp, count(bakedgood) 
FROM generate_series( 
    (SELECT min(firstbaked) FROM sparse_dates),
    (SELECT max(firstbaked) FROM sparse_dates),
    INTERVAL '1' MONTH) 
    AS monthtimestamp
LEFT OUTER JOIN sparse_dates ON (monthtimestamp = firstbaked)
GROUP BY monthtimestamp;

with output like:

     monthtimestamp     | count 
------------------------+-------
 2012-02-01 00:00:00+08 |     2
 2012-03-01 00:00:00+08 |     1
 2012-04-01 00:00:00+08 |     0
 2012-05-01 00:00:00+08 |     0
 2012-06-01 00:00:00+08 |     0
 2012-07-01 00:00:00+08 |     0
 2012-08-01 00:00:00+08 |     1
(7 rows)

PostgreSQL – Select into Specific Array Positions with array_agg()

Your answer basically gets the job done:

SELECT b.id, array_agg(b.stock) AS stock
FROM  (
   SELECT i.id, COALESCE(i_s.stock, 0) AS stock
   FROM   item i
   CROSS  JOIN unnest('{1,2}'::int[]) n
   LEFT   JOIN item_stock i_s ON i.id = i_s.item_id AND n.n = i_s.shop_id
   ORDER  BY i.id, n.n
   ) b
GROUP  BY b.id;

Two notable changes:

Order is not guaranteed without ORDER BY in the subquery or as additional clause to array_aggregate() (typically more expensive). And that's the core element of your question.
unnest('{1,2}'::int[]) instead of generate_series(1,2) as requested shop IDs will hardly be sequential all the time.

I also moved the set-returning function from the SELECT list to a separate table expression attached with CROSS JOIN. Standard SQL form, but that's just a matter of clarity and taste, not a necessity. At least in Postgres 10 or later. See:

What is the expected behaviour for multiple set-returning functions in SELECT clause?

Doing the same with LEFT JOIN LATERAL and an ARRAY constructor might be a bit faster as we don't need the outer GROUP BY and the ARRAY constructor is typically faster, too:

SELECT i.id, s.stock
FROM   item i
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT COALESCE(i_s.stock, 0)
      FROM   unnest('{1,2}'::int[]) n
      LEFT   JOIN item_stock i_s ON i_s.shop_id = n.n
                                AND i_s.item_id = i.id
      ORDER  BY n.n
      ) AS stock
   ) s;

And if you have more than just the two shops, a nested crosstab() should provide top performance:

SELECT i.id, COALESCE(stock, '{0,0}') AS stock
FROM   item i
LEFT   JOIN (
   SELECT id, ARRAY[COALESCE(shop1, 0), COALESCE(shop2, 0)] AS stock
   FROM   crosstab(
     $$SELECT item_id, shop_id, stock
       FROM   item_stock
       WHERE  shop_id = ANY ('{1,2}'::int[])
       ORDER  BY 1,2$$

     , $$SELECT unnest('{1,2}'::int[])$$
      ) AS ct (id int, shop1 int, shop2 int)
   ) i_s USING (id);

Needs to be adapted in more places to cater for different shop IDs.

PostgreSQL Crosstab Query

db<>fiddle here

Index

Make sure you have at least an index on item_stock (shop_id, item_id) - typically provided by a PRIMARY KEY on those columns. For the crosstab query, it also matters that shop_id comes first. See:

Is a composite index also good for queries on the first field?

Adding stock as another index expression may allow faster index-only scans. In Postgres 11 or later consider an INCLUDE item to the PK like so:

PRIMARY KEY (shop_id, item_id) INCLUDE (stock)

But only if you need it a lot, as it makes the index a bit bigger and possibly more susceptible to bloat from updates.

Best Answer

Related Solutions

Postgresql – Combine a generated series with each row from a select

PostgreSQL – Select into Specific Array Positions with array_agg()

Index

Related Question