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

Question

我在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

我已添加了一个区间列（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: 子查询作为表达式返回了多行

我该如何继续，才能使用时间差查询的结果来更新time_diff列的值？

- jatobat

---

不确定您的应用程序逻辑，但选择返回可能更多的行，这会在分配到单个列中时（就像您在UPDATE中所做的那样）导致错误...如果结果在您的SELECT中返回的第一行中，则使用LIMIT 1使分配成为可能。无论如何，选择似乎设计不正确。 - Kamil Šrot

@KamilŠrot - 这里有一个问题；因为子查询是（目前）不相关的，LIMIT 1 只会返回顶部行，而不是与当前行有关的行。 - Clockwork-Muse

@Clockwork-Muse 对，这就是我为什么说查询设计不正确的原因。首先，@jatobat 需要创建一个仅返回一个值的查询（根据应用程序逻辑所需的值），然后将其作为子查询放入UPDATE查询中...通常情况下，在子查询的WHERE子句中使用来自正在更新的表（响应行）的一些标识符/值作为条件。但坦白地说：我不理解应用程序的逻辑，甚至不想理解 :-) - Kamil Šrot

Answer 1

像这样的东西：

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;

- user330315

---

+1。这看起来对我来说是正确的，并且比我快了4分钟。直到现在我才知道WITH... AS...的存在。很酷。 :) - Mark Amery

@MarkAmery：这被称为“公共表达式”，自9.1版本以来，它也可以用于DML语句（在此之前，它只能用于普通的SELECT语句 - 包括递归查询）。 - user330315

如果您正在使用T-SQL进行此操作，则似乎无法为更新表设置别名。不过，如果您明确使用表名（而不是在此情况下的tr），则可以正常工作。 - Spencer Kershaw

@SpencerKershaw：好吧，问题标记为“postgresql”，所以很明显这个答案不适用于SQL Server。 - user330315

你的回答非常有用，尤其是对于SQL来说，正是我所需要的。我想其他人也可能会遇到同样的问题。 - Spencer Kershaw

Answer 2

您不能在UPDATE中直接使用窗口函数，因此需要在子查询中使用它 - 这是您已经完成的。但是，您尝试在UPDATE中使用该子查询的方式不是有效的语法。您需要将子查询放在UPDATE的FROM子句中，如Postgres文档所解释的那样：

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

你想要做的正确语法是：

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}}

显然，你需要在我写的 {{SomeUniqueId}} 处替换为你的行具有的一些唯一 id 的列名。

- Mark Amery

Answer 3

实际上，您之所以会出现此错误，是因为您的子查询返回了多个结果，

我无法理解您的查询，因此，

我将给您一个示例来解决它，

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

这里：-your_operation 意味着找到时间差的逻辑。

- Hunter

---

是的，这是他需要做的一般形式...也许填写查询的其余部分？ - Clockwork-Muse

这个答案对我有用，我在使用PostgreSQL 9.0.1时遇到了问题。谢谢。 - Ambran