PostgreSQL last_value ignore nulls

Question

I know this already been asked, but why doesn't the solution below work? I want to fill value with the last non-null value ordered by idx.

What I see:

 idx | coalesce 
-----+----------
   1 |        2
   2 |        4
   3 |         
   4 |         
   5 |       10
(5 rows)

What I want:

 idx | coalesce 
-----+----------
   1 |        2
   2 |        4
   3 |        4 
   4 |        4 
   5 |       10
(5 rows)

Code:

with base as (

    select 1    as idx
         , 2    as value

    union

    select 2    as idx
         , 4    as value

    union

    select 3    as idx
         , null as value

    union

    select 4    as idx
         , null as value

    union

    select 5    as idx
         , 10   as value
)

select idx
     , coalesce(value
              , last_value(value) over (order by case when value is null then -1
                                                 else idx
                                                 end))
from base
order by idx

Answer 1

What you want is lag(ignore nulls). Here is one way to do what you want, using two window functions. The first defines the grouping for the NULL values and the second assigns the value:

select idx, value, coalesce(value, max(value) over (partition by grp))
from (select b.*, count(value) over (order by idx) as grp
      from base b
     ) b
order by idx;

You can also do this without subqueries by using arrays. Basically, take the last element not counting NULLs:

select idx, value, 
       (array_remove(array_agg(value) over (order by idx), null))[count(value) over (order by idx)]
from base b
order by idx;

Here is a db<>fiddle.