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.

pests

id | name ---+-------------- 1  | Thrip  2  | Fungus Gnats 

pest_counts

id | pest_id | date         | count ---+---------+--------------+------- 1  | 1       | 2015-01-01   | 14 2  | 2       | 2015-01-02   | 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 ...