Thread: default select ordering

Please accept my apologies if this is answered elsewhere in the archives
or docs but I have searched without luck.

I've always assumed that default ordering of selects are based on a first
in first out principle and that this remains true at the row level despite
edits to columns.

However I'm dealing with a case where this doesn't seem to hold true. The
ordering has changed over time. Its difficult for me to gauge whether the
data has been moved around with pg_dump or whether optimisation/vacuum
commands have been called but I'm wondering if either of these things
could effect the first in first out ordering and if there's any system
tables or configerations that I can use to either verify or prevent this.

Any help greaty appreciated.

Matt Roberts

Matt Roberts <matt@runtime-collective.com> writes:

> Please accept my apologies if this is answered elsewhere in the archives
> or docs but I have searched without luck.
>
> I've always assumed that default ordering of selects are based on a first
> in first out principle and that this remains true at the row level despite
> edits to columns.

There is no guaranteed ordering of rows without an ORDER BY clause.
VACUUM will change the row ordering as it moves tuples around to free
up space.  If you want a guaranteed order, use a key field and ORDER BY.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863