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.

Answer 2

Well the last_value here doesn't make sense to me unless you can point out to me. Looking at the example you need the last non value which you can get it by: I am forming a group with the nulls and previous non null value so that I can get the first non value.

with base as (
select 1    as idx , 2    as value   union
select 2    as idx, -14    as value    union
select 3    as idx , null as value   union
select 4    as idx , null as value   union
select 5    as idx , 1   as value
)
Select idx,value,
first_value(value) Over(partition by rn) as new_val
from(
select idx,value
    ,sum(case when value is not null then 1 end) over (order by idx) as rn
  from   base
) t

here is the code

http://sqlfiddle.com/#!15/fcda4/2

Answer 3

To see why your solution doesn't work, just look at the output if you order by the ordering in your window frame:

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, value from base
order by case when value is null then -1
                                                 else idx
                                                 end;
 idx | value
-----+-------
   3 |
   4 |
   1 |     2
   2 |     4
   5 |    10

The last_value() window function will pick the last value in the current frame. Without changing any of the frame defaults, this will be the current row.