Changelog WITH ORDINALITY improvements
Changelog

WITH ORDINALITY improvements

08.21.2025

WITH ORDINALITY adds a numbering column to table function output. For example, you can number list items when unnesting lists:

1 
CREATE TABLE quizzes(scores int list);
2 
INSERT INTO quizzes VALUES (LIST[5, 7, 8]), (LIST[3, 3]);
3
4 
SELECT scores, score, ordinality
5 
FROM
6 
  quizzes,
7 
  unnest(scores) WITH ORDINALITY AS t(score, ordinality);
sql
1 
scores  | score | ordinality
2 
---------+-------+------------
3 
 {3,3}   |     3 |          1
4 
 {3,3}   |     3 |          2
5 
 {5,7,8} |     5 |          1
6 
 {5,7,8} |     7 |          2
7 
 {5,7,8} |     8 |          3
8 
(5 rows)
text

You can use the ROWS FROM clause to zip the outputs of multiple table functions rather than taking their cross product:

1 
SELECT *
2 
FROM
3 
  ROWS FROM (
4 
    generate_series(1, 2),
5 
    generate_series(6, 7)
6 
  ) AS t(g1, g2);
sql
1 
g1 | g2
2 
----+----
3 
  1 |  6
4 
  2 |  7
5 
(2 rows)
text

We have rewritten how we implement the WITH ORDINALITY and ROWS FROM clauses, and thus fixed a performance issue and an incorrect ordering bug.