Fill nulls with lag, but ignore nulls isn't supported

Question

I have a table which looks like this:

ID	money_earned	days_since_start
1	1000	1
1	2000	2
1	null	3
1	3000	4
1	2000	5
2	1000	1
2	null	2
2	100	3

I want that rows, without a value in money_earned (which means that the money_earned column was empty that day) - will and fill the money_earned with last known value, so it to look like this:

ID	money_earned	days_since_start
1	1000	1
1	2000	2
1	2000	3
1	3000	4
1	2000	5
2	1000	1
2	1000	2
2	100	3

I have tried to look up for something like that, but postgresql doesn't support ignore nulls in lag window function :(

thank you!

https://stackoverflow.com/questions/47719010/postgresql-ignore-nulls-in-window-functions — , Apr 29 '22 at 11:10
Does this answer your question? [PostgreSQL IGNORE NULLS in window functions](https://stackoverflow.com/questions/47719010/postgresql-ignore-nulls-in-window-functions) — DhruvJoshi, Apr 29 '22 at 11:16
Are there any chances for any random ID, where days_since_start is equal to 1 but it has null value in the money_earned field. Will it consider the money_earned from a previous ID? — EbixG, Apr 29 '22 at 12:45

emrdev · Accepted Answer · 2022-11-23T10:19:13.047

The LOG() function would be suitable for this purpose, but in your case you need to get first not null previous element. What the LOG() function can't handle.

I suggest creating your own function that would return the first non-null value of the previous element.

CREATE OR REPLACE FUNCTION log_not_null(row_num int, dafult text = '1001') RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  result text; 
  index int;
BEGIN 
  if row_num > 0  then 
      index := row_num - 1;
   else
      return dafult;
   end if;
   result := (select money_earned from my_table  offset index limit 1);
   if result is null then 
     return log_not_null(index); 
   end if;  
   return result;
END
$$;


select id,  log_not_null((row_number() over ())::int) as money_earned, days_since_start from my_table;

Note:

If the previous value is null, then the function is called recursively until it reaches the top element, and if the topmost element is also null, then the function will return the value from the dafult variable.

Demo in sqldaddy.io

@NightHawk I have changed the solution, try it, maybe this will solve your problem) — emrdev, Apr 29 '22 at 15:40

score 1 · Answer 2 · answered Apr 29 '22 at 15:59

You can do this (warning, untested! ):

UPDATE yourtable t1 
SET money_earned = t3.money_earned
FROM yourtable 
LEFT JOIN (SELECT 
                ID,
                MAX(days_since_start) m
           FROM yourtable 
           WHERE ID=t1.ID AND days_since_start<t1.ID) t2 ON t2.ID=i1.ID
INNER JOIN yourtable t3 ON t3.ID=t1.ID and t3.days_since_start = t2.days_since_start
WHERE t1.money_earned is null

Fill nulls with lag, but ignore nulls isn't supported

2 Answers2