1

Postgres 9.4 introduced the WITH ORDINALITY clause which adds an ordinality column to a table-like expression, somewhat similar to ROW_NUMBER() OVER (). Apparently, this is used to provide ranks for set-returning functions like unnest(<array>). My question is, is the order of rows defined for VALUES lists? I would like very much for this to be the case:

 SELECT *, ROW_NUMBER() OVER () FROM (VALUES ('a'), ('b'), ('c')) xs(a);
 a | row_number 
---+------------
 a |          1
 b |          2
 c |          3

EDIT: Fixed to use ROW_NUMBER instead of WITH ORDINALITY (which is syntactically incompatible with a values list).

0

1 Answer 1

1

VALUES is treated like SELECT. The SELECT documentation says:

  1. If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Your version of Postgres happens to return the three rows in the order you have written them. While unlikely, this could change in the future.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.