Filling Postgres table gaps with oldest available value-CodePudding

I am trying to fill the missing colored rows with the earliest value which remains valid until a new value is set using SQL queries in Postgres database

CodePudding user response：

You can use first_value() like this:

select 
    client, 
    activity_date,
    val,
    first_value(val) over (partition by client, grp_t) as new_val
from (
      select client, activity_date, val,
             sum(case when val is not null then 1 end) over (order by client, activity_date) as grp_t
      from   filling
) t
order by 1,2,3;

Here's dbfiddle example

upd. Fixed new_val due to issue when the first row per client is NULL