Parallel unnest() and sort order in PostgreSQL

by

Yes, that is a feature of Postgres and parallel unnesting is guaranteed to be in sync (as long as all arrays have the same number of elements).
Postgres 9.4 adds a clean solution for parallel unnest:

The order of resulting rows is not guaranteed, though. Actually, with a statement as simple as:

SELECT unnest(ARRAY[5,3,9]) AS id;

the resulting order of rows is “guaranteed”, but Postgres does not assert anything. The query optimizer is free to order rows as it sees fit as long as the order is not explicitly defined. This may have side effects in more complex queries.

If the second query in your question is what you actually want (add an index number to unnested array elements), there is a better way with generate_subscripts():

SELECT unnest(ARRAY[5,3,9]) AS id
     , generate_subscripts(ARRAY[5,3,9], 1) AS idx
ORDER  BY idx;

Details in this related answer:

You will be interested in WITH ORDINALITY in Postgres 9.4:

Then you can use:

SELECT * FROM unnest(ARRAY[5,3,9]) WITH ORDINALITY tbl(id, idx);

More Related Contents:

  1. Split given string and prepare case statement
  2. PostgreSQL unnest() with element number
  3. How do I query using fields inside the new PostgreSQL JSON datatype?
  4. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  5. Split column into multiple rows in Postgres
  6. Call a set-returning function with an array argument multiple times
  7. What’s the proper index for querying structures in arrays in Postgres jsonb?
  8. Record returned from function has columns concatenated
  9. Query for element of array in JSON column
  10. How to return two columns with function
  11. PostgreSQL 9.3: Dynamic pivot table
  12. Select rows which are not present in other table
  13. Optimize GROUP BY query to retrieve latest row per user
  14. How to implement a many-to-many relationship in PostgreSQL?
  15. Check if value exists in Postgres array
  16. How to use ANY instead of IN in a WHERE clause?
  17. Is there a shortcut for SELECT * FROM?
  18. sql statement error: “column .. does not exist”
  19. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  20. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  21. Sorting null values after all others, except special
  22. Reference alias in WHERE clause
  23. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  24. Why postgres returns unordered data in select query, after updation of row?
  25. Continuing a transaction after primary key violation error
  26. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  27. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  28. Function with SQL query has no destination for result data
  29. Postgres INTERVAL using value from table
  30. Truncate timestamp to arbitrary intervals

Leave a Comment