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