On 3rd of October 2025, Tatsuo Ishii committed patch:
Add IGNORE NULLS/RESPECT NULLS option to Window functions. Add IGNORE NULLS/RESPECT NULLS option (null treatment clause) to lead, lag, first_value, last_value and nth_value window functions. If unspecified, the default is RESPECT NULLS which includes NULL values in any result calculation. IGNORE NULLS ignores NULL values. Built-in window functions are modified to call new API WinCheckAndInitializeNullTreatment() to indicate whether they accept IGNORE NULLS/RESPECT NULLS option or not (the API can be called by user defined window functions as well). If WinGetFuncArgInPartition's allowNullTreatment argument is true and IGNORE NULLS option is given, WinGetFuncArgInPartition() or WinGetFuncArgInFrame() will return evaluated function's argument expression on specified non NULL row (if it exists) in the partition or the frame. When IGNORE NULLS option is given, window functions need to visit and evaluate same rows over and over again to look for non null rows. To mitigate the issue, 2-bit not null information array is created while executing window functions to remember whether the row has been already evaluated to NULL or NOT NULL. If already evaluated, we could skip the evaluation work, thus we could get better performance. Author: Oliver Ford <ojford@gmail.com> Co-authored-by: Tatsuo Ishii <ishii@postgresql.org> Reviewed-by: Krasiyan Andreev <krasiyan@gmail.com> Reviewed-by: Andrew Gierth <andrew@tao11.riddles.org.uk> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: David Fetter <david@fetter.org> Reviewed-by: Vik Fearing <vik@postgresfriends.org> Reviewed-by: "David G. Johnston" <david.g.johnston@gmail.com> Reviewed-by: Chao Li <lic@highgo.com> Discussion: https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
This is pretty cool, and easy to show.
Let's make a test table:
=$ drop table the_test; DROP TABLE =$ create table the_test ( id int8 generated always as identity primary key, some_group text, some_value text ); CREATE TABLE =$ copy the_test (some_group, some_value) from stdin with ( DELIMITER ',', NULL '🕱' ); a,🕱 b,🕱 a,2 a,3 a,🕱 a,1 b,14 a,4 b,15 b,13 a,🕱 b,11 b,12 b,🕱 a,5 b,🕱 a,🕱 b,🕱 \. COPY 18 =$ \pset null '🕱' Null display is "🕱". =$ select * from the_test; id | some_group | some_value ----+------------+------------ 1 | a | 🕱 2 | b | 🕱 3 | a | 2 4 | a | 3 5 | a | 🕱 6 | a | 1 7 | b | 14 8 | a | 4 9 | b | 15 10 | b | 13 11 | a | 🕱 12 | b | 11 13 | b | 12 14 | b | 🕱 15 | a | 5 16 | b | 🕱 17 | a | 🕱 18 | b | 🕱 (18 rows)
So, how does it work, let's see using very simple query:
=$ select id, some_group, some_value, lag(some_value) over my_window, lag(some_value) ignore nulls over my_window as lag_in, lead(some_value) over my_window, lead(some_value) ignore nulls over my_window as lead_in, first_value(some_value) over my_window, first_value(some_value) ignore nulls over my_window as first_value_in, last_value(some_value) over my_window, last_value(some_value) ignore nulls over my_window as last_value_in, nth_value(some_value, 4) over my_window, nth_value(some_value, 4) ignore nulls over my_window as nth_value_in from the_test window my_window as (partition by some_group order by id) order by some_group, id; id | some_group | some_value | lag | lag_in | lead | lead_in | first_value | first_value_in | last_value | last_value_in | nth_value | nth_value_in ----+------------+------------+-----+--------+------+---------+-------------+----------------+------------+---------------+-----------+-------------- 1 | a | 🕱 | 🕱 | 🕱 | 2 | 2 | 🕱 | 🕱 | 🕱 | 🕱 | 🕱 | 🕱 3 | a | 2 | 🕱 | 🕱 | 3 | 3 | 🕱 | 2 | 2 | 2 | 🕱 | 🕱 4 | a | 3 | 2 | 2 | 🕱 | 1 | 🕱 | 2 | 3 | 3 | 🕱 | 🕱 5 | a | 🕱 | 3 | 3 | 1 | 1 | 🕱 | 2 | 🕱 | 3 | 🕱 | 🕱 6 | a | 1 | 🕱 | 3 | 4 | 4 | 🕱 | 2 | 1 | 1 | 🕱 | 🕱 8 | a | 4 | 1 | 1 | 🕱 | 5 | 🕱 | 2 | 4 | 4 | 🕱 | 4 11 | a | 🕱 | 4 | 4 | 5 | 5 | 🕱 | 2 | 🕱 | 4 | 🕱 | 4 15 | a | 5 | 🕱 | 4 | 🕱 | 🕱 | 🕱 | 2 | 5 | 5 | 🕱 | 4 17 | a | 🕱 | 5 | 5 | 🕱 | 🕱 | 🕱 | 2 | 🕱 | 5 | 🕱 | 4 2 | b | 🕱 | 🕱 | 🕱 | 14 | 14 | 🕱 | 🕱 | 🕱 | 🕱 | 🕱 | 🕱 7 | b | 14 | 🕱 | 🕱 | 15 | 15 | 🕱 | 14 | 14 | 14 | 🕱 | 🕱 9 | b | 15 | 14 | 14 | 13 | 13 | 🕱 | 14 | 15 | 15 | 🕱 | 🕱 10 | b | 13 | 15 | 15 | 11 | 11 | 🕱 | 14 | 13 | 13 | 13 | 🕱 12 | b | 11 | 13 | 13 | 12 | 12 | 🕱 | 14 | 11 | 11 | 13 | 11 13 | b | 12 | 11 | 11 | 🕱 | 🕱 | 🕱 | 14 | 12 | 12 | 13 | 11 14 | b | 🕱 | 12 | 12 | 🕱 | 🕱 | 🕱 | 14 | 🕱 | 12 | 13 | 11 16 | b | 🕱 | 🕱 | 12 | 🕱 | 🕱 | 🕱 | 14 | 🕱 | 12 | 13 | 11 18 | b | 🕱 | 🕱 | 12 | 🕱 | 🕱 | 🕱 | 14 | 🕱 | 12 | 13 | 11 (18 rows)
As you can see addition of ignore nulls removed almost all null values from output.
The remaining ones are either:
- because when using window functions with order by clause, window function works only on subset of rows that have the same some_group (like for example first_value value for row with id == 1)
- or there is no value available, like on first row and lag()
If, instead of ignore nulls I'd have respect nulls – I would get back to default output.
Looks pretty cool. Thanks to everyone involved in making this a reality.