a task that occurs quite frequently in data warehouse systems is to delete some rows from a table and add these rows to an archive table. It would be nice to have a syntax to do this transfer in one step (instead of using one of the options shown recently by Jonathan Lewis in https://jonathanlewis.wordpress.com/2016/11/22/deleteinsert/ ). In postgres you can use subquery factoring and the returning clause to get this task done - as shown in the following example:
postgres=# select * from t1;
id
----
1
2
3
4
5
6
7
8
9
10
(10 Zeilen)
postgres=# create table t2 as select * from t1 where 1 = 0;
SELECT 0
postgres=# select * from t2;
id
----
(0 Zeilen)
postgres=# with moved_rows as (delete from t1 where id <= 5 returning *)
postgres-# insert into t2 select * from moved_rows;
INSERT 0 5
postgres=# select * from t1;
id
----
6
7
8
9
10
(5 Zeilen)
postgres=# select * from t2;
id
----
1
2
3
4
5
(5 Zeilen)