Re: [pgsql-bugs] Daily digest v1.1282 (5 messages)

Brendan,

> Description:        update does not honor order of subselect
>
> Details:
>
> SQL Update command does not follow the order of a WHERE field IN subselect.
>
> In the following code, I try to reset the order of rows in a column by
> updating an order field.  Update does not honor the order of output form
> the subselect.

This is by design. ORDER BY for anything but output is a PostgreSQL
extension, which we support strictly so that subselects can use LIMIT. The
SQL standard does not allow ORDER BY in subselects, and is pretty specific on
query results not being ordered until the final execution step. Also, you
should not count on subselect order in any subquery, because the subselect
resultset may need to be re-ordered in order to join to the main query --
particularly since the PostgreSQL planner may re-write your query into a more
efficient join type.

If you need to update rows in a specific order, then write a script and use a
loop.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco