Re: weirdness with the a sql update

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:

> Hi,
>
> I just noticed this, if I do a update like this:
>
> update new_requests set name = 'tony' where request_id = 2
>
> If I do a select * from new_requests that record I just updated is now
> at the bottom , before the update it was at the top?
>
> Why is Postgresql changing the ordering of the results after a simple
> update?

Results are returned in an arbitrary order unless you supply an ORDER
BY clause in your query.

> It almost looks like the record is being dropped and then readded to
> the end.

Yup.  In Postgres, UPDATE == DELETE + INSERT, so the new row will very
likely go into a different place (this is so that existing
transactions can still see the old row before your transaction
commits).

-Doug