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)

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
   ;