Nacef LABIDI wrote:
> Yes I don't issue any sort statement, and I indeed want the data to be show
> as it is stored in the database.
That's what you're getting. PostgreSQL has an MVCC design. How it works
in general terms
(simplified, but I lack the expertise to give a complete explanation
even if one was required) is:
When you update a row the old row is marked as dead and a new row is
inserted. If there is no
spare space in the table near the old row (assuming there's any reason
for the DB to even try
to put the new row near the old one) then the new row will be placed
elsewhere, such as at
the end of the table.
In other words, after an UPDATE the row really is often at the end of
In any case as others have explained you should never rely on the
of records; you should always use an ORDER BY if you care about order.
makes no guarantees about the order of returned rows.
The database may optimise row retrieval in ways you do not expect. For
in recent versions of PostgreSQL if there is a sequential scan in
progress on a table
and you start another query that also runs a sequential scan on the
may synchronize the two scans. That'll cause your query to start part
way through the
table. If the table contains alphabetically ordered data you might get
... etc ...
So ... in any database, always use ORDER BY if you care about order.
Just because it usually
works in some databases doesn't mean it won't break just rarely enough
to drive you insane
In response to
pgsql-sql by date
|Next:||From: Bart Degryse||Date: 2008-04-22 12:56:00|
|Subject: Re: After updating dataset the record goes to the end of thedataset|
|Previous:||From: Craig Ringer||Date: 2008-04-22 12:30:28|
|Subject: Re: Problems with function while migrating from Windows to