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
– Gordon Linoff, Commented May 26, 2016 at 21:14

Chris · Accepted Answer · 2022-11-02 15:00:08Z

22

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 2, 2022 at 15:00

Chris

139k139 gold badges316 silver badges293 bronze badges

answered Nov 10, 2017 at 13:03

Sebastien

6,7105 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lukas Eder Over a year ago

But that's not really the same thing as the IGNORE NULLS clause.

Papipo Over a year ago

A clause that postgresql does not support atm

Kliment Merzlyakov Over a year ago

This would not work if you are looking for the non-null first value after the current row. It will take the first non-null value for this user_id no matter of the position of the current row.

Christopher Lorenz · Accepted Answer · 2017-12-23 23:20:28Z

9

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;

answered Dec 23, 2017 at 23:20

Christopher Lorenz

911 silver badge3 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2016-05-26 21:18:09Z

3

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, 2016 at 21:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

user3558238 Over a year ago

The t.id in the t2.id >= t.id filter isn't being found when I run this

Gordon Linoff Over a year ago

@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.

user3558238 Over a year ago

it's saying the t.user_id does not exist, perhaps subqueries can't refer to outer query parameters in PostgreSQL?

Gordon Linoff Over a year ago

@user3558238 . . . Postgres definitely supports correlated subqueries. You should edit your question and include your attempt.

Zegarek · Accepted Answer · 2024-03-08 14:12:31Z

2

Aggregate functions can be used as window functions
There's an aggregate filter clause.
Window spec can tell it to order most recent first. Get them into an array:

select (array_agg(COL1)filter(where COL1 is not null)over w1)[1]
from cte
window w1 as (order by d desc rows 
              between current row and unbounded following);

You pop the array[1].

answered Mar 8, 2024 at 14:12

Zegarek

30k5 gold badges27 silver badges32 bronze badges

1 Comment

Zegarek Over a year ago

Nice thing is, it's not just skip nulls, it's anything you can express in the where and it combines lag, lead, first, last and nth_value being able to target any position, which can also be an expression switching it dynamically. Looks and performance aside, one downside might be that if you want a negative subscript (supported for json arrays but not for the regular ones), you need to flip the window, or aggregate to json array, or bounce off of upper bound: (array_agg()over w1)[count()over w1 -3] or repeat array_agg() and take its array_upper().

Ashwaq · Accepted Answer · 2019-07-17 06:51:10Z

-2

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, 2019 at 6:51

Ashwaq

4691 gold badge8 silver badges17 bronze badges

Comments

HEX · Accepted Answer · 2020-07-11 19:40:24Z

-3

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    |

answered Jul 11, 2020 at 19:40

HEX

1,6932 gold badges15 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

3 Comments

Comments

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related