Обсуждение: Updating record drops it to the bottom

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

--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