综合技术

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

微信扫一扫,分享到朋友圈

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

我在PostgreSQL 9.2中有下表,其中包含时间戳:

gid [PK](bigserial),timestamp_mes(没有时区的时间戳),time_diff(interval)

1,2012-01-23 11:03:40,空的

2,2012-01-23 11:03:42,空的

3,2012-01-23 11:03:44,空的

我添加了一个间隔列(time_diff),并希望用此查询产生的时差值填充它:

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

我尝试了以下查询来更新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);

这会导致错误:

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

我应该如何使用时差查询生成的值更新time_diff列?

像这样的东西:

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;

翻译自:https://stackoverflow.com/questions/13767908/updating-a-column-with-the-results-of-a-query-in-postgresql

阅读原文...


代码日志

android – 清除WebView创建的所有缓存项目?

上一篇

Mental well-being took center stage at CES 2019

下一篇

您也可能喜欢

评论已经被关闭。

插入图片
使用PostgreSQL中的查询结果更新列

长按储存图像,分享给朋友