CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Oto Shavadze
Oto Shavadze

Reputation: 42753

Ordered result from unnest() function

There is guarantee that function unnest() will be return values in these order, how they are located?

That is, for exmple from this:

arr (INTEGER[])
---------------
{{3,5},{33,3}}

This query:

SELECT unnest(arr) FROM "table"

Always will be return

 3
 5
 33
 3

?

Or may be result will be ordered differently, than array elements are ordered?

Upvotes: 2

Views: 3761

Answers (1)

roman
roman

Reputation: 117337

Here's helpful link for you UNNEST result order vs Array data.

Here's an assumption:

An unnest() will almost certainly scan the array in-order, but once you embed that in a large query the ordering is no longer guaranteed.

Quote from this thread:

unnest() returns its output in the same order as the input. Since an array is ordered it will be returned in the same output order by unnest. However, since unnest() only returns a single column (though possibly of a composite type) it cannot provide the row number in the output thus in order to maintain the same order elsewhere in the query it is necessary to use "ROW_NUMBER() OVER (...)" on the output of the unnest() - and before joining it with any other unnest calls or tables - before supplying it to the rest of the query. The "WITH ORDINALITY" functionality proposed for 9.4 will cause the unnest() [and other] function to output this additional column along with the usual output. This is, I am pretty such, a usability enhancement that makes easier something that can be done today using CTE/WITH and/or sub-queries.

Another useful links:

Upvotes: 5

Related Questions