CODEWITHSUNDEEP
sqlpostgresqltime-series
Jason Nordwick
Jason Nordwick

Reputation: 1556

Adding column to table with value from next row

I have a table in PostgreSQL with a timestamp column, and I want to modify the table to have a second timestamp column and seed it with the value of the immediately successive timestamp. Is there a way to do this? The tables are fairly large, so a correlated subquery might kill the machine.

More concretely, I want to go from this to that:

+----+------+           +----+------+------+
| ts | data |           | ts | te   | data |
+----+------+           +----+------+------+
| T  | ...  |    -->    | T  | U    | ...  |
| U  | ...  |           | U  | V    | ...  |
| V  | ...  |           | V  | null | ...  |
+----+------+           +----+------+------+

Basically, I want to be able to hand point in time queries much better (i.e., give me the data for time X).

Upvotes: 1

Views: 1251

Answers (2)

kumar
kumar

Reputation: 1

select ts,LEAD(ts) over(order by (select null)) as te,data from table_name

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Basically I think you could just retrieve the timestamp at the query time, not storing it in the table, but if you're performing such action and think that this is what you need then:

You need to add that column to your table:

ALTER TABLE tablename ADD COLUMN te timestamp;

Then perform an update feeding the value with the use of LEAD window function.

UPDATE tablename t
SET te = x.te
FROM (
  SELECT ts, lead(ts, 1) OVER (order by ts) AS te
  FROM tablename t2
  ) x
WHERE t.ts = x.ts

Here's an example of how it works using sample integer data: SQL Fiddle.

It will perform exactly the same for timestamp data type values.

Upvotes: 1

Related Questions