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;

Convert Multiple Row output from SELECT query into Single Row in PostgreSQL

Here is the simple query to convert the result of select query from multiple rows in a table to display as single row,


Simple SELECT query

SELECT str_empno FROM employee;

will return the output like,


Output:

str_empno
-------------
4400100476
4400100563
4400101130
4400101432
4400101501
4400101527
4400101724

by using array_agg and array_to_string function in PostgreSQL,  we can get aggregates/combine/concatenate multiple rows into one row

SELECT array_to_string( array_agg(str_empno),',' ) as  empno FROM employee;

this will return 
Output:

str_empno
-------------

4400100476,4400100563,4400101130,4400101432,4400101501,4400101527,4400101724