my question is closely related to the following question asked in this thread generate_series for multiple record types in postgresql. The only difference is, that instead of only inserting missing values, I want to fill the gaps with the last previous value. To illustrate it with the same example. I have two tables.
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);
The desired output should be nearly identical. However, I want to fill missing values with previous values, if possible. So the second entry for Thrip should be 14, since a previous value exists and the first value of Fungus Gnats should be 0, since no previous value exists. Is it possible to do this using Postgres?
expected results
name | date | count
-------------+------------+-------
Thrip | 2015-01-01 | 14
Thrip | 2015-01-02 | **14** <- fill with existing previous value.
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...