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?

You need a column to specify the ordering. SQL tables are inherently unordered. — Gordon Linoff, May 26 '16 at 21:14

score 17 · Answer 1 · edited Nov 02 '22 at 15:00

17

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 compared to skip nulls because the database will have to sort on the additional criteria.

edited Nov 02 '22 at 15:00

ChrisGPT was on strike

127,765
105
273
257

answered Nov 10 '17 at 13:03

Sebastien

5,506
4
27
37

1

But that's not really the same thing as the `IGNORE NULLS` clause. – Lukas Eder Apr 29 '21 at 13:29
1

A clause that postgresql does not support atm – Papipo Aug 09 '21 at 22:18

score 8 · Answer 2 · answered Dec 23 '17 at 23:20

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;

score 1 · Answer 3 · answered May 26 '16 at 21:18

1

There is always the tried and true approach of using a correlated subquery:

select t.*,
       (select t2.col1
        from t t2
        where t2.id >= t.id and t2.col1 is not null
        order by t2.id desc
        fetch first 1 row only
       ) as nextcol1
from t;

answered May 26 '16 at 21:18

Gordon Linoff

1,242,037
58
646
786

The t.id in the t2.id >= t.id filter isn't being found when I run this – user3558238 May 26 '16 at 21:53
@user3558238 . . . What do you mean it isn't being found? `t` is the alias of the table in the outer query; `t2` is the alias in the inner query. – Gordon Linoff May 26 '16 at 23:06
it's saying the t.user_id does not exist, perhaps subqueries can't refer to outer query parameters in PostgreSQL? – user3558238 May 27 '16 at 14:16
@user3558238 . . . Postgres definitely supports correlated subqueries. You should edit your question and include your attempt. – Gordon Linoff May 28 '16 at 21:38

score -1 · Answer 4 · answered Jul 17 '19 at 06:51

-1

Hope this helps,

SELECT * FROM TABLE ORDER BY COALESCE(colA, colB);

which orders by colA and if colA has NULL value it orders by colB.

answered Jul 17 '19 at 06:51

Ashwaq

431
7
17

score -1 · Answer 5 · answered Jul 11 '20 at 19:40

You can use COALESCE() function. For your query:

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

but i don't understand what the reason to use sort by COL2, because this rows has null value for COL2:

 | User_id |   COL1   | COL2 |
 +---------+----------+------+
 | 1       |          | 1    |
 | 1       |          | 2    | 
 | 1       |   2421   |      | <<--- null?
 | 1       |          | 1    | 
 | 1       |   3542   |      | <<--- null?
 | 2       |          | 1    |

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

5 Answers5

Linked