How to ignore nulls in PostgreSQL window functions? or return the next non-null value in a column

Question

Lets say I have the following table:

 | User_id |   COL1   | COL2 |
 +---------+----------+------+
 | 1       |          | 1    |
 | 1       |          | 2    | 
 | 1       |   2421   |      | 
 | 1       |          | 1    | 
 | 1       |   3542   |      | 
 | 2       |          | 1    |

I need another column indicating the next non-null COL1 value for each row, so the result would look like the below:

 | User_id |   COL1   | COL2 | COL3 |
 +---------+----------+------+------
 | 1       |          | 1    | 2421 |
 | 1       |          | 2    | 2421 |
 | 1       |   2421   |      |      |
 | 1       |          | 1    | 3542 |
 | 1       |   3542   |      |      |
 | 2       |          | 1    |      |

---

SELECT 
first_value(COL1 ignore nulls) over (partition by user_id order by COL2 rows unbounded following) 
FROM table;

---

would work but I'm using PostgreSQL which doesn't support the ignore nulls clause.

Any suggested workarounds?

Answer 1

You can still do it with windowing function if you add a case when criteria in the order by like this:

select
   first_value(COL1) 
   over (
     partition by user_id 
     order by case when COL1 is not null then 0 else 1 end ASC, COL2 
     rows unbounded following
   ) 
from table

This will use non null values first.

However performance will probably not be great comapred to skip nulls because the database will have to sort on the additional criteria.

Answer 2

I also had the same problem. The other solutions may work, but I have to build multiple windows for each row I need.

You can try this snippets : https://wiki.postgresql.org/wiki/First/last_(aggregate)

If you create the aggregates you can use them:

SELECT 
first(COL1) over (partition by user_id order by COL2 rows unbounded following) 
FROM table;