Forward (or Backward filling) in postgres

Question

The problem is to fill missing values in a table. In pandas, one can use forward (or backward) filling to do so as shown below:

$> import pandas as pd
$> df = pd.DataFrame({'x': [None, 1, None, None, 2, None, 3, None]})
$> df['y'] = df['x'].fillna(method='ffill')
$> df
    x   y
0 NaN NaN
1   1   1
2 NaN   1
3 NaN   1
4   2   2
5 NaN   2
6   3   3
7 NaN   3

Is there a way to do that in SQL and more precisely in PostGres? I guess window functions could help but i couldn't figure out how.

In PostGres, it would be like:

sandbox=# SELECT x, ??
FROM
  (SELECT NULL AS x
   UNION ALL SELECT 1 AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT 2 AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT 3 AS x
   UNION ALL SELECT NULL AS x) a;
 x 
---

 1


 2

 3

(8 rows)

Stock

where is the table? what missing values are you trying to fill out? – Forever

I edited it, i forgot one crucial line of pandas which uses the fillna with forward filling that i want to reproduce on postgres – Stock

you will need function here - if you just lag window function here - you will get previous x, nut not next to previous. In other words you want some kind of RECURSIVE LAG here, to repeat not previous value, but previous defined value – Shan

I tried to cheat with last know good value in window. not sure if it what you are looking for – Shan

Answer 1

window functions here

so many aliases since your query is very sensitive to order. I added more empty x lines to prove it is prune to several empty lines...

select x,y from (
select r,x, case when y is not null then y else min(y) over (partition by x order by r) end y from (
SELECT row_number() over() r,x, case when x is not null then x else lag(x) over () end y
FROM
  (SELECT NULL AS x
   UNION ALL SELECT 1 AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT 2 AS x
   UNION ALL SELECT NULL AS x
   UNION ALL SELECT 3 AS x
   UNION ALL SELECT NULL AS x
   ) a
   ) b
order by r
   ) c
   ;

Shan

This is not generalizable as per answer from @Philip-Hurvitz – Glennisglennon

Answer 2

The last answer works but is not generalizable. Here I just add another NULL value after record 7:

select unnest(array [null,1,null,null,null,null, 2, null, null,3]) as x

I also turn the query into a CTE for readability:

with
    a as (select unnest(array [null,1,null,null,null,null, 2, null, null,3]) as x
    )
  , b as (
    SELECT
        row_number() over () as r
      , x
      , case
            when x is not null
                then x
            else lag(x) over ()
            end              as y
    FROM
        a
)
  , c as (
    select
        r
      , x
      , case
            when y is not null
                then y
            else min(y) over (partition by x order by r)
            end as z
    from
        b
    order by r
)
select
    x
  , z
from
    c
;

Record 9 is part of the partition of NULL values and it takes on the MIN() of that partition, which is 1.

I do not have a generalizable solution, but just wanted to caution against the use of the previously shown method

Spiegleman

Answer 3

Recursive view works and is generalizable.

#1 - generating a base table with mising data samples:

create temp table base as 
with a as (select unnest(array [null,1,null,null,null,null, 2, null, null,3]) as value)
, b as (SELECT row_number() over () as id, value FROM a)
select * from b;

select * from base;

#2 create a recursive view:

WITH RECURSIVE base_ffill AS (
  SELECT id, value
  FROM base
  WHERE value IS NOT NULL
  UNION ALL
  SELECT a.id, b.value as value_ffill
  FROM base a
  JOIN base_ffill b ON a.id = b.id + 1
  WHERE a.value IS NULL
)
SELECT id,  value
FROM base_ffill
ORDER BY 1;

Glennisglennon