Updating a column with query results in PostgreSQL

Question

I have the following table in PostgreSQL 9.2 that contains timestamps:

gid [PK] (bigserial), timestamp_mes (timestamp without time zone), time_diff (interval)
1, 2012-01-23 11:03:40, empty
2, 2012-01-23 11:03:42, empty
3, 2012-01-23 11:03:44, empty

I added a column interval (time_diff) and would like to populate it with the time difference values ​​obtained from this query:

SELECT timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff from gc_entretien.trace order by timestamp_mes 

I tried the following query to update the time_diff column without success:

 UPDATE gc_entretien.trace set time_diff = (SELECT trace.timestamp_mes - lag(trace.timestamp_mes, 1) over (order by trace.timestamp_mes) from gc_entretien.trace order by timestamp_mes); 

This will result in an error:

ERROR: more than one row returned by the subquery used as expression

How can I switch to updating the time_diff column with the values ​​obtained as a result of requesting the time difference?

Answer 1

Something like that:

 with new_values as ( SELECT gid, timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff from gc_entretien.trace ) update gc_entretien.trace as tr set time_diff = nv.diff from new_values nv where nv.gid = tr.gid; 

Answer 2

You cannot directly use the window function in UPDATE, so you need to use it in the sub-SELECT you made. However, the way you tried to use this sub-SELECT in your UPDATE is invalid syntax. You should put sub-SELECT in the FROM clause of your update, as described in the Postgres docs:

http://www.postgresql.org/docs/9.2/static/sql-update.html

The correct syntax for what you want to do:

 UPDATE gc_entretien.trace t SET time_diff = subquery.diff FROM (SELECT {{SomeUniqueId}}, timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff FROM gc_entretien.trace order by timestamp_mes) AS subquery WHERE t.{{SomeUniqueId}} = subquery.{{SomeUniqueId}} 

Obviously, you will need to substitute a unique identifier in the column name, which is on your lines where I wrote {{SomeUniqueId}}

Answer 3

You actually get this error because your subquery returns multiple results,

I can’t understand your request so

I will give you an example to solve it,

 update table t1 set time_diff= select *your_operation* from table t2 where t1.id=t2.id 

Here : -your_operation means the logic of finding the time difference,