generate_series for multiple record types in postgresql

Question

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

Answer 1

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.

Answer 2

I will suggest next time that you use fiddle.com in order to have an online schema to play with.

generate_series function returns a set of timestamp, so you will need to cast it to date outside the function. This is necessary in the current query as the timestamp won't match the date in the pest_counts table.

sandbox=# \df generate_series
   Schema   |      Name       |         Result data type          |                        Argument data types                         |  Type  
(...)
 pg_catalog | generate_series | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | normal
 pg_catalog | generate_series | SETOF timestamp with time zone    | timestamp with time zone, timestamp with time zone, interval       | normal
(6 rows)

I will suggest something like:

SELECT p.name, pc.date, pc.count 
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 day') days 
join pest_counts pc ON (days::date = pc.date) 
join pests p ON (p.id = pc.pest_id) ;