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

Brendan,

> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0update does not honor order of su=
bselect
>
> Details:
>
> SQL Update command does not follow the order of a WHERE field IN subselec=
t.
>
> In the following code, I try to reset the order of rows in a column by
> updating an order field. =C2=A0Update does not honor the order of output =
form
> the subselect.

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

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

--=20
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco