Re: Reordering a table

On Tue, Feb 22, 2011 at 04:40:36PM +0000, Howard Cole wrote:
- Hi,
-
- a puzzle to solve...
-
- I have a table with a primary key, and a timestamp, e.g.
-
- id    stamp
- 1     2011-02-01 10:00
- 2     2011-02-01 09:00
- 3     2011-02-01 11:00
-
- Now for reasons too painful to go into, I need to reorder the id
- (sequence) so that they are in time order:
-
- id    stamp
- 1     2011-02-01 09:00
- 2     2011-02-01 10:00
- 3     2011-02-01 11:00
-
- I thought I could do it by adding a third colum with the order in it,
- but I cannot think of a way to do this short of writing some code
-
- id    stamp                         order
- 1     2011-02-01 10:00    2
- 2     2011-02-01 09:00    1
- 3     2011-02-01 11:00    3
-
- Any ideas?
-
- Thanks.
- Howard
- www.selestial.com

There is almost certianly a slicker way, but what comes to the top of my head is

create new_table (id serial, stamp timestamp)
insert into new_table (stamp) select stamp from old_table order by stamp;
rename old_table old_table_old -- just to be safe
rename new_table old_table

this, of course, doesn't stop stuff from getting out of order again.

Dave