Moving table data from one table to another

Yeah, we can move table rows from one table to another table. Here is how:

WITH deleted_rows AS (
DELETE FROM source_table WHERE id = 1
RETURNING *
)
INSERT INTO destination_table
SELECT * FROM deleted_rows;

Example

Create two tables

SELECT * FROM test1 ;
 id |  name
----+--------
  1 | yogesh
  2 | Raunak
  3 | Varun
(3 rows)

SELECT * FROM test2;
 id | name
----+------
(0 rows)

Move rows from table test1 to table test2

WITH deleted_rows AS (
  DELETE FROM test1 WHERE id = 1
  RETURNING *
  )
  INSERT INTO test2
  SELECT * FROM deleted_rows;
INSERT 0 1

Verify

SELECT * FROM test2;
 id |  name
----+--------
  1 | yogesh
(1 row)

select * from test1;
 id |  name
----+--------
  2 | Raunak
  3 | Varun