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