Tuesday, November 6, 2012

Return Inserted, Updated and Deleted rows/record of the Table in PostgreSQL



 Query to get return last Inserted, Updated and Deleted record in PostgreSQL

this will work PostgreSQL 9.1 and above

it return the updated row, it update the values in the table and also return the updated record.

WITH tmp AS 
  (
   UPDATE login_user SET user_name = '1'||user_name 
    where username = 'abs'
   RETURNING *
  )
SELECT * FROM tmp;

Also return specific column instead of full row in RETURNING clause

WITH tmp AS 
  (
   UPDATE login_user SET user_name = '1'||user_name 
    where username = 'abs'
   RETURNING  id, user_name
  )
SELECT * FROM tmp;

Insert all the deleted row into another table by

WITH tmp AS  (
   DELETE  FROM login_user WHERE user_name ='abc'
    RETURNING *  )
INSET INTO t1 select * from tmp;





Post a Comment