row_number() always return 1 since the source table has only one row

Question

Let's consider this minimal example:

SELECT row_number() over() as GID, 
       unnest(myarray)     as letter
FROM 
(
    SELECT string_to_array('a,b,b', ',') as myarray
) AS T

Now since the table T contains only one element, the row_number() function always return 1 even if 3 rows are returned.

So I get:

GID   letter
1     "a"
1     "b"
1     "b"

Instead of

GID   letter
1     "a"
2     "b"
3     "b"

How can it be fixed ?

Answer 1

Put the unnest clause right around the string_to_array call, so that T has multiple rows already:

SELECT row_number() over() as GID, myarray as letter
FROM (
    SELECT unnest(string_to_array('a,b,c', ',')) as myarray
) AS T

Also I would recommend using WITH ORDINALITY instead of row_number() to get indexed array elements:

SELECT gid, letter
FROM UNNEST(string_to_array('a,b,c', ',')) WITH ORDINALITY AS T(gid, letter)

This also works if you want to keep the array-returning select expression

SELECT gid, letter
FROM UNNEST(
  (SELECT string_to_array('a,b,c', ',') as myarray)
) WITH ORDINALITY AS T(letter, gid)

or use a lateral query:

SELECT gid, letter
FROM
  (SELECT string_to_array('a,b,c', ',') as myarray) AS T,
  UNNEST(T.myarray) WITH ORDINALITY AS U(letter, gid)