Window function IGNORE NULLS workaround for PostgreSQL [duplicate]

Question

This question already has answers here:

Carry over long sequence of missing values with Postgres (2 answers)

Closed 5 years ago.

With the following query I can use the LAG() function to repeat the last non null value of c column:

SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000) open_time,
 coalesce(o, LAG(c) over w) o,
 coalesce(h, LAG(c) over w) h,
 coalesce(l, LAG(c) over w) l,
 coalesce(c, LAG(c) over w) c,
 coalesce(v, 0) v,
 coalesce(close_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000 + ((60000 * 1) - 1)) close_time
from temporary_timeframe 
window w as (order by datetime);

Getting the following result:

enter image description here

But I need to repeat the value of the c column while the current column value is null. I see that if PostgreSQL supports IGNORE NULLS attribute on window functions this would be solved. How to solve this without IGNORE NULLS?

Answer 1

LAG() doesn't repeat the last non null value.

_{Quoted from docs}

returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value)

But you can set a partition depending on one column value and then use firts_value() function.

create table tbl (id int, a int, b int, c int);

✓

insert into tbl values
(1, 12, 4, 3),
(2, 10, 10, 5),
(3, 6, 12, 23),
(4, 6, null, 10),
(5, 7, null, 4),
(6, 1, 8, 10),
(7, 4, null, 3);

7 rows affected

select id, a, first_value(b) over (partition by grp) as b, c
from (
 select id, a, b, c, 
 sum(case when b is not null then 1 end) over (order by id) as grp
 from tbl
 ) t

id | a | b | c
-: | -: | -: | -:
 1 | 12 | 4 | 3
 2 | 10 | 10 | 5
 3 | 6 | 12 | 23
 4 | 6 | 12 | 10
 5 | 7 | 12 | 4
 6 | 1 | 8 | 10
 7 | 4 | 8 | 3

db<>fiddle here