How to get the last non-null value that came before the current row Say I have the following table:
create table tab (ts integer, a integer);

insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3);

When i query the data there may be some nulls:
select * from tab;

+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | (null) |
|  4 | (null) |
|  5 | 3      |
+----+--------+

Now I like to replace the null values with the first non-null value that comes before it. Like so:
+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | 2      |
|  4 | 2      |
|  5 | 3      |
+----+--------+

I've tried using several variations of the FIRST_VALUE and LAST_VALUE window functions but they always only return first value overall, the last value overall or the current value.
SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a IS NULL THEN NULL ELSE ts END NULLS LAST
  )
FROM
  tab
ORDER BY
  ts ASC;

-- OR

SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a is NULL then 0 else 1 END DESC, ts
  )
FROM
  tab
ORDER BY
  ts ASC;

+----+--------+-------------+
| ts |   a    | first_value |
+----+--------+-------------+
|  1 | 1      |           1 |
|  2 | 2      |           1 |
|  3 | (null) |           1 |
|  4 | (null) |           1 |
|  5 | 3      |           1 |
+----+--------+-------------+
You can use a subquery:
select
  t1.ts,
  case when a is null then
    (select a 
     from tab t2 
     where t2.ts &lt; t1.ts and a is not null 
     order by a desc
     limit 1)
  else a end as a
from
  tab t1;



ts |  a
-: | -:
 1 |  1
 2 |  2
 3 |  2
 4 |  2
 5 |  3


db&lt;&gt;fiddle here