I have a table that looks like this:

| column | def  |
| ------ | ---- |
| id     | uuid |
| status | int  |
| data   | text |

I'd like to write a query to duplicate a row (changing only a few columns) and return the old (replaced) data:

INSERT INTO tmp (status, data)
SELECT source.status, :data
FROM table AS source
WHERE id = :id
RETURNING id, source.data

but RETURNING can only access values from the inserted row, is there an alternative to do this?


You can 'store' your old data in a CTE, then do the change in another (writable) CTE, and return the original values like this:

WITH old AS (
    SELECT status, data
      FROM your_table
     WHERE id = :id 
new AS (
    INSERT INTO your_table
    SELECT status, :data
      FROM old

The writable CTE (new) works on the results of the previous one (old).

  • thx this lead me in the right direction. I just had to SELECT new.id, new.status, old.status FROM old, new
    – Mathieu
    Sep 23 '15 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.