Deleting rows from a table and inserting into another table in one query

It is relatively easy to insert rows from one table that match a certain condition into another table, e.g.:
INSERT INTO table2 (col1, col2) SELECT mycol99 as col1, col2 FROM table1 WHERE id < 1000 AND col2 IS NOT NULL;

This inserts rows from table1 that match the SQL condition id < 1000 AND col2 IS NOT NULL into table2. It uses the column mycol99 from table1 as the value for col1 (in table2) and col2 as col2.

Sometimes you want to ‚move‘ the rows from one table to another, i.e. INSERT some rows into table2, then delete them from table1. This can be done with:

INSERT INTO table2 (col1, col2) SELECT mycol99 as col1, col2 FROM table1 WHERE id < 1000 AND col2 IS NOT NULL; DELETE FROM table1 WHERE id < 1000 AND col2 IS NOT NULL;

This will work, and if you run it all within a transaction, it will be consistant (I think). However it’s not æstheically pleasing. You are doing things in 2 statements, when you only conceptially mean to do one action. It breaks „Don’t Repeat Yourself“ (DRY), the WHERE condition is repeated. If you want to change the WHERE condition, you have to do it in 2 places.

Luckily, as of PostgreSQL 9, you can do this all in one statement:

WITH output AS ( delete from table1 where id < 1000 AND col2 IS NOT NULL RETURNING mycol99 as col1, col2) INSERT INTO table2 (col1, col2) SELECT col1, col2 FROM output;"

Simples.