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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
