PostgreSQL – Using generate_series for Multiple Record Types

postgresql

I have two tables that I want to query: pest_counts and pests which look like:

CREATE TABLE pests(id,name)
AS VALUES
  (1,'Thrip'),
  (2,'Fungus Gnosts');

CREATE TABLE pest_counts(id,pest_id,date,count)
AS VALUES
  (1,1,'2015-01-01'::date,14),
  (2,2,'2015-01-02'::date,5);

I want to use postgres' generate_series to show the number of each type of pest that was found for the date series:

expected results

name         | date       | count
-------------+------------+-------
Thrip        | 2015-01-01 | 14
Thrip        | 2015-01-02 | 0
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...

I know I'll need something like the following but I'm not exactly sure how to do the rest:

SELECT date FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 day') date

Best Answer

I usually solve such problems by setting up a table for all the possible data points (here the pests and dates). This is easily achieved by a CROSS JOIN, see the WITH query below.

Then, as the finishing step, I just (outer) join the existing measurements, based on the pest ID and date - optionally giving a default for the missing values via COALESCE().

So, the whole query is:

WITH data_points AS (
    SELECT id, name, i::date
    FROM pests
    CROSS JOIN generate_series('2015-01-01'::date, '2015-01-05', '1 day') t(i)
) 
SELECT d.name, d.i, COALESCE(p.cnt, 0) 
FROM data_points AS d 
LEFT JOIN pest_counts AS p 
    ON d.id = p.pest_id 
    AND d.i = p.count_date;

Check it at work on SQLFiddle.

Note: when either the table(s) or the generated series are big, doing the CROSS JOIN inside a CTE might be a bad idea. (It has to materialize all the rows, regardless of there is data for a given day or not). In this case one should do the same in the FROM clause, as a parenthesized sub-join instead of the current reference to data_points. This way the planner has a better understanding about the rows affected and the possibilities for using indexes. I use the CTE in the example because it looks cleaner for the sake of the example.

Related Question