sql - Cannot SELECT from UPDATE RETURNING clause in postgres

Question

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'); 

If I run the query:

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

I'm getting the following error:

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

*** Fehler ***

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

However if I only run the statemennt

UPDATE test set value='test' RETURNING id

I get a result with 2 rows:

1 2

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

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

with the result:

1;"test" 2;"test"

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

Answer 1

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

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);

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.