Updating record drops it to the bottom

--On Tuesday, November 26, 2002 07:16:42 -0800 "Aurangzeb M. Agha"
<aagha@bigfoot.com> wrote:

I've noticed that whenever I update a record in the table and do a select
* to see my change, that the updated racord has dropped to the bottom of
the table.

Is there either a) a (simple) way to keep this from happening or b)
putting the record back where it "belongs" after an update.

The reason is is annoying me is that one of our internal apps has onld
piece of JS which builds sub menus on a form based on the (DB driven)
order of the parent menu. Yes, yes... the solution is to fix the JS, but
until the JS guy gets back, the DB solution is what comes to mind.

There is no order defined by SQL. If you want a particular order, add an
ORDER BY
to the SQL.

Thx in advance,
Aurangzeb

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

"Aurangzeb M. Agha" <aagha@bigfoot.com> writes:

I've noticed that whenever I update a record in the table and do a select
* to see my change, that the updated racord has dropped to the bottom of
the table.

Is there either a) a (simple) way to keep this from happening or b)
putting the record back where it "belongs" after an update.

Postgres (and most other SQL databases) makes no guarantee about the
order of results from a SELECT unless you use an ORDER BY clause. So
put ORDER BY in your query if you care about the display order.

-Doug

Aurangzeb M. Agha wrote:

I've noticed that whenever I update a record in the table and do a
select * to see my change, that the updated racord has dropped to the
bottom of the table.

Is there either a) a (simple) way to keep this from happening or b)
putting the record back where it "belongs" after an update.

The reason is is annoying me is that one of our internal apps has onld
piece of JS which builds sub menus on a form based on the (DB driven)
order of the parent menu. Yes, yes... the solution is to fix the JS,
but until the JS guy gets back, the DB solution is what comes to mind.

Thx in advance,
Aurangzeb

AS has been stated ORDER BY is the correct solution, however you may be able
to get the functionality you want by CLUSTER after the update, which will
physically reorder the rows based upon an index. Be careful of doing this if
you have anything thats dependent on this table (eg views) in case it loses
its reference (as I believe CLUSTER basically copies out the table to a new
file).
The reason it drops to the bottom is that an unordered table scan select
will just return the rows in the order it finds them. As postgresql has a
none over writing storage manager it just places new rows at the end of the
file (or in space marked as free in >=pg7.2).
hth,
- Stuart