使用PostgreSQL中的查询结果更新列

I have the following table in PostgreSQL 9.2 which contains time stamps:

我在PostgreSQL 9.2中有以下表格，其中包含时间戳:

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

2012-01-23 11:03:40, empty 2, 2012-01-23 11:03:42, empty 3, empty 3, 2012-01-23 11:03:44, empty 2, empty 3

I have added a interval column (time_diff) and would like to fill it with time difference values resulting from this query:

我已经添加了一个interval列(time_diff)，并希望用这个查询产生的时差值填充它:

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

I have tried the following query to update the time_diff column, with no success:

我尝试了以下查询来更新time_diff列，但没有成功:

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 results in an error:

这导致一个错误:

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

错误:作为表达式的子查询返回的行数

How should I proceed to update the time_diff column with the values resulting from the time difference query?

如何使用时间差异查询生成的值更新time_diff列?

3 个解决方案

#1

Something like this:

是这样的:

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;

#2

You can't directly use a window function in an UPDATE, so you instead need to use it in a sub-SELECT - which you have done. However, the way you've tried to use that sub-SELECT in your UPDATE is not valid syntax. You need to put the sub-SELECT in the FROM clause of your update, as explained by the Postgres docs here:

您不能在更新中直接使用窗口函数，因此您需要在子选择中使用窗口函数——您已经这样做了。但是，您尝试在更新中使用子选择的方式是无效语法。您需要在更新的FROM子句中添加子select，正如Postgres文档所解释的那样:

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

The correct syntax for what you want to do is:

你想做的正确语法是:

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'll need to substitute in the column name of some unique id that your rows have where I've written {{SomeUniqueId}}

显然，您需要将您的行所具有的某个惟一id的列名替换为{{SomeUniqueId}

#3

Actually you are getting this error because your subquery returns multiple result,

实际上你会得到这个错误因为你的子查询返回多个结果，

I am not able to understand your query 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 time difference,

这里:-your_operation表示查找时间差的逻辑，

#1