无法从Postgres中的UPDATE RETURNING子句中进行选择 [英] Cannot SELECT from UPDATE RETURNING clause in postgres

问题描述

我从一个更复杂的查询中隔离了这个问题。这里的测试场景

I isolatet the problem from a much more complex query. Here the test scenario

DROP TABLE test; 
CREATE TABLE test (
  id integer,
  description varchar(100)
);

INSERT INTO test(id, description) VALUES (1,'new'); 
INSERT INTO test(id, description) VALUES (2,'new'); 

如果我运行查询：

SELECT * FROM test WHERE id IN (UPDATE test set description='test' RETURNING id)

我遇到以下错误：

错误：测试或附近时出现语法错误
第1行：SELECT * FROM测试WHERE ID（更新测试集描述=测试 RE ...
^

ERROR: syntax error at or near "test" LINE 1: SELECT * FROM test WHERE id (UPDATE test set description='test' RE... ^

*** Fehler ** *

*** Fehler ***

错误：语法错误在附近 test
SQL状态：42601
Zeichen：37

ERROR: syntax error at or near "test" SQL Status:42601 Zeichen:37

但是，如果我只运行statemennt

However if I only run the statemennt

UPDATE test set value='test' RETURNING id

我得到2行结果：

1
2

1 2

如果我屈服该结果将有一个查询，例如：

If I substitude that result I would have a query like:

SELECT * FROM test WHERE id IN (1,2);

与t他的结果是：

1；测试
2；测试

1;"test" 2;"test"

为什么

Why do I not get the same result with my initial statement?

推荐答案

在PostgreSQL 9.1之前INSERT / UPDATE / DELETE只能用作顶层陈述。这就是为什么会出现语法错误的原因。

Before PostgreSQL 9.1 INSERT/UPDATE/DELETE could only be used as top level statements. This is why you are getting a syntax error.

从9.1开始，您可以使用带有公共表表达式的数据修改语句。您的示例查询如下所示：

Starting from 9.1 you can use data-modifying statements with common table expressions. Your example query would look like this:

WITH updated AS (UPDATE test SET description = 'test' RETURNING id)
SELECT * FROM test WHERE id IN (SELECT id FROM updated);

请注意从刚修改的表中进行选择。这样您会得到令人困惑的结果。因为查询是在同一快照中执行的，所以SELECT将看不到UPDATE语句的效果。

Be careful with selecting from the just modified table. You can get confusing results that way. Becuse the queries are executed in the same snapshot, the SELECT will not see the effects of the UPDATE statement.

这篇关于无法从Postgres中的UPDATE RETURNING子句中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！