Cannot Select from Update Returning Clause in Postgres

Cannot SELECT from UPDATE RETURNING clause in postgres

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.

Is using the RETURNING clause from an UPDATE as the query clause for an INSERT's query clause possible?

Right now, no.

There was a feature that almost made it into PostgreSQL 9.0 known as Writeable CTE's that does what you're thinking (although the syntax is different).

Currently, you could either do this via a trigger or as two separate statements.

Return pre-UPDATE column values using SQL only

Problem

The manual explains:

The optional RETURNING clause causes UPDATE to compute and return
value(s) based on each row actually updated. Any expression using the
table's columns, and/or columns of other tables mentioned in FROM, can
be computed. The new (post-update) values of the table's columns are
used. The syntax of the RETURNING list is identical to that of the
output list of SELECT.

Bold emphasis mine. There is no way to access the old row in a RETURNING clause. You can work around this restriction with a trigger or a separate SELECT before the UPDATE wrapped in a transaction or wrapped in a CTE as was commented.

However, what you are trying to achieve works perfectly fine if you join to another instance of the table in the FROM clause:

Solution without concurrent writes

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Returns:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

The column(s) used to self-join must be UNIQUE NOT NULL. In the simple example, the WHERE condition is on the same column tbl_id, but that's just coincidence. Works for any conditions.

I tested this with PostgreSQL versions from 8.4 to 13.

It's different for INSERT:

• INSERT INTO ... FROM SELECT ... RETURNING id mappings

Solutions with concurrent write load

There are various ways to avoid race conditions with concurrent write operations on the same rows. (Note that concurrent write operations on unrelated rows are no problem at all.) The simple, slow and sure (but expensive) method is to run the transaction with SERIALIZABLE isolation level:

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ... ;
COMMIT;

But that's probably overkill. And you need to be prepared to repeat the operation in case of a serialization failure.

Simpler and faster (and just as reliable with concurrent write load) is an explicit lock on the one row to be updated:

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Note how the WHERE condition moved to the subquery (again, can be anything), and only the self-join (on UNIQUE NOT NULL column(s)) remains in the outer query. This guarantees that only rows locked by the inner SELECT are processed. The WHERE conditions might resolve to a different set of rows a moment later.

See:

• Atomic UPDATE .. SELECT in Postgres

db<>fiddle here

_{Old sqlfiddle}

SELECT FOR UPDATE returns zero rows

I'd say this is a bug in PostgreSQL, and you should report it.

UPDATE with RETURNING: Not able to retrieve ResultSet

Well, I wasn’t really able to solve this properly. However, I ended up using a workaround by wrapping the whole SQL request with a SELECT statement:

PreparedStatement statement = this.prepareStatement(
    "WITH results AS (" +
        "WITH temp AS (" +
        "    SELECT id" +
        "    FROM mytable " +
        "    LIMIT 5 "
        ") " +
        "UPDATE mytable " +
        "SET updated = NOW() " +
        "FROM temp " +
        "WHERE temp.id = mytable.id " +
        "RETURNING mytable.data" +
    ") " +
    "SELECT * FROM results"
);

ResultSet result = statement.executeQuery();

while (result.next()) {
    // Work with result
}

Store the result of an UPDATE with a RETURNING clause on PLPGSQL

It doesn't matter if it SELECT, INSERT or UPDATE. When you specify RETURNING it works the same way as it was SELECT. So you can write:

UPDATE table SET column = new_value RETURNING check INTO <your_variable>

More to that, you can use the results in the same query with the help of CTE:

WITH updated AS (
    UPDATE table SET column = new_value RETURNING check
)
SELECT check FROM updated ...

PostgreSQL return table with update and select statement causing ambiguity

You cannot table-qualify the column to be updated, but you can (and must, in this case) table-qualify columns in the expression used:

SET  viewcount = us.viewcount + 1

BTW, in Postgres 8.0 or later, you can (and should) use parameter names instead of aliases:

CREATE OR REPLACE FUNCTION submission_getone (
  _submissionid int
 ,_upcount boolean
 ,_mmask int
 ,_statemask int
)

... and get get rid of this:

declare
    _submissionid   alias for $1;
    _upcount        alias for $2;
    _mmask          alias for $3;
    _statemask      alias for $4;

RETURNS TABLE indicates you got at least Postgres 8.4.

FROM clause with DO UPDATE in Postgres

Your sub-select has the wrong structure.

If you need to update multiple columns, use a tuple assignment

INSERT INTOcustomers (name, email, city) 
SELECT 'jack',
       'jack@io' as email,
       city 
FROM customers 
where id = 1 
ON CONFLICT(name) DO 
  update set (email, other_col) = (select email, c2
                                   from customers 
                                   where id = 1)

updating table rows in postgres using subquery

Postgres allows:

UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.

---

Related Topics