WITH ORDINALITY clause

The WITH ORDINALITY clause can be used with set functions in the FROM clause of a query. An additional integer column will be added to the table, which numbers the rows returned by the function, starting from 1. By default, the generated column is named ordinality.

See Set functions for a list of supported set functions.

Here is a simple example of how the WITH ORDINALITY clause works.

SELECT * FROM unnest(array[0,1,2]) WITH ORDINALITY;

The output will be as follows.

 unnest | ordinality 
--------+------------
      0 |          1
      1 |          2
      2 |          3

If we have a table t like so:

   arr   
---------
 {a,b,c}
 {d,e}

We can use the unnest function on the column arr, call WITH ORDINALITY, and rename the newly generated columns.

SELECT * FROM t CROSS JOIN unnest(t.arr) WITH ORDINALITY AS x(elts, num);

The results will be as follows.

   arr   | elts | num 
---------+------+-----
 {a,b,c} | c    |   3
 {a,b,c} | b    |   2
 {a,b,c} | a    |   1
 {d,e}   | e    |   2
 {d,e}   | d    |   1