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

On Feb 22, 2011, at 10:10 PM, 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?

If you are using PG 8.4 then you can try something with row_number as given below:
select id,stamp, row_number() over(order by stamp) from test;

Or

Create table test1 as select row_number() over(order by stamp) as id, stamp from test;

Thanks & Regards,
Vibhor Kumar

Something like:

SELECT
ordered.stamp,
nextval('sequence') AS rownumber
FROM (SELECT stamp FROM table ORDER BY stamp ASC) ordered

Incorporate the ID field and UPDATE as necessary to get the result the way
you need it.

You are apparently aware that you likely have a design or understanding
issue due to the fact that you need to do this persistently.

You may be able to accomplish a similar result without the use of a sequence
by using WINDOW but for a one-off scenario the sequence should suffice.

David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Howard Cole
Sent: Tuesday, February 22, 2011 11:41 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Reordering a table

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 22/02/2011 5:18 PM, Vibhor Kumar wrote:

If you are using PG 8.4 then you can try something with row_number as
given below:
select id,stamp, row_number() over(order by stamp) from test;

Or

Create table test1 as select row_number() over(order by stamp) as id, stamp from test;

Thanks& Regards,
Vibhor Kumar

I have not come across that that function before. I'll take a closer look.

Many thanks to all for the quick responses.

Howard.