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 ...