Cannot SELECT from UPDATE RETURNING clause in postgres

Question

I isolated 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 statement

UPDATE test set value='test' RETURNING id

I get a result with 2 rows:

1 2

If I substitute 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?

They have to be separate statements. The fact that two rows are being updated is likely to cause issues, never tried RETURNING when more than one row/value is coming back. — OMG Ponies, Aug 25 '11 at 14:17

score 53 · Accepted Answer · answered Aug 25 '11 at 22:27

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.

So this `SELECT` would return the id and the old description value `'new'` instead up the updated value `'test'` ? — Davos, Nov 13 '17 at 00:43

JK. · Answer 2 · 2011-08-25T15:02:20.697

11

You update two rows in your UPDATE query, add a WHERE clause to restrict the rows affected.

UPDATE test SET description = 'test' WHERE id = 1 RETURNING id

to return a single row.

edited Aug 25 '11 at 15:02

answered Aug 25 '11 at 14:17

JK.

5,126
1
27
26

SELECT * FROM test WHERE id IN (UPDATE test SET value='test' WHERE id=1 TURNING id) shows the same error – markus Aug 25 '11 at 14:25

score 5 · Answer 3 · edited Oct 10 '12 at 19:26

5

UPDATE test set description='test' RETURNING *

would give you the result set that you expect from the initial query.

But I suspect you were trying something more complex?

edited Oct 10 '12 at 19:26

j0k

22,600
28
79
90

answered Jul 20 '12 at 13:09

RMS

51
1
1

score 2 · Answer 4 · answered Aug 25 '11 at 14:18

2

Are you missing an IN: ... WHERE id IN (UPDATE ...?

However if I only run the statemennt "UPDATE test set value='test' RETURNING id", I get a result with 2 rows. Why is that?

Your UPDATE has no WHERE clause and therefore it updates every row, of which there are two.

answered Aug 25 '11 at 14:18

NPE

486,780
108
951
1,012

1

The issue is not the update RETURNING, that is working with and without where clause. The issue is with the select around it. – markus Aug 25 '11 at 14:28

score 0 · Answer 5 · answered Oct 05 '15 at 14:12

DROP TABLE IF EXISTS test_tab;

CREATE TABLE test_tab (
  id integer,
  description varchar(100)
);

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

SELECT * from test_tab;

DO $$
DECLARE
    myID    test_tab.id%TYPE;
    testID  test_tab.id%TYPE;
    cur_IDs CURSOR for select id from test_tab;
BEGIN
    OPEN cur_IDs;
    LOOP
        FETCH cur_IDs into testID;
        EXIT WHEN testID is NULL;

        UPDATE test_tab SET description='test' WHERE id = testID RETURNING id into myID;
        raise notice 'myID %', myID;
    END LOOP;
    CLOSE cur_IDs;
END$$;


DROP TABLE IF EXISTS test_tab;

score 0 · Answer 6 · answered Aug 25 '11 at 14:20

0

You are not limiting your where clause. You need to have id = (blahblah) or id IN (blahblah)

answered Aug 25 '11 at 14:20

Tony318

552
2
9

score -1 · Answer 7 · answered Oct 20 '16 at 08:13

-1

I'm addition from Ants Aasma, if select on same table, using:

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

answered Oct 20 '16 at 08:13

bnson

222
3
7

Cannot SELECT from UPDATE RETURNING clause in postgres

7 Answers7

Linked

Related