StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h3>Problem</h3> <p><a href="http://www.postgresql.org/docs/current/interactive/sql-update.html" rel="nofollow noreferrer">The manual explains</a>:</p> <blockquote> <p>The optional <code>RETURNING</code> clause causes <code>UPDATE</code> 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 <code>FROM</code>, can be computed. The <strong>new (post-update) values of the table's columns are used</strong>. The syntax of the <code>RETURNING</code> list is identical to that of the output list of <code>SELECT</code>.</p> </blockquote> <p>Emphasis mine. There is no way to access the old row in a <code>RETURNING</code> clause. You can do that in a trigger or with a separate <code>SELECT</code> <em>before</em> the <code>UPDATE</code>, wrapped in a transaction as @Flimzy and @wildplasser commented, or wrapped in a CTE as @MattDiPasquale posted.</p> <h3>Solution</h3> <p>However, what you are trying to achieve <strong>works perfectly fine</strong> if you join in another instance of the table in the <code>FROM</code> clause:</p> <pre><code>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; </code></pre> <p>Returns:</p> <pre><code> old_id | old_name | tbl_id | name --------+----------+--------+--------- 3 | Old Guy | 23 | New Guy </code></pre> <p><a href="http://www.sqlfiddle.com/#!12/7150a/2" rel="nofollow noreferrer"><strong>SQL Fiddle.</strong></a></p> <p>I tested this with PostgreSQL versions from 8.4 to 9.6.</p> <p>It's different for <code>INSERT</code>:</p> <ul> <li><a href="https://stackoverflow.com/questions/29256888/insert-into-from-select-returning-id-mappings/29263402#29263402">INSERT INTO ... FROM SELECT ... RETURNING id mappings</a></li> </ul> <h3>Dealing with concurrent write load</h3> <p>There are several ways to avoid race conditions with concurrent write operations. The simple, slow and sure (but expensive) method is to run the transaction with <code>SERIALIZABLE</code> isolation level.</p> <pre><code>BEGIN ISOLATION LEVEL SERIALIZABLE; UPDATE ..; COMMIT; </code></pre> <p>But that's probably overkill. And you'd need to be prepared to repeat the operation if you get a serialization failure.<br> Simpler and faster (and just as reliable with concurrent write load) is an explicit lock on the <em>one</em> row to be updated:</p> <pre><code>UPDATE tbl x SET tbl_id = 24 , name = 'New Gal' FROM (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 <b>FOR UPDATE</b>) 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; </code></pre> <p>More explanation, examples and links under this related question:</p> <ul> <li><a href="https://stackoverflow.com/questions/11532550/atomic-update-select-in-postgres">Atomic UPDATE .. SELECT in Postgres</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload