move data from one table to another, postgresql edition

Question

I'd like to move some data from one table to another (with a possibly different schema). Straightforward solution that comes into mind is -

start a transaction with serializable isolation level; INSERT INTO dest_table SELECT data FROM orig_table,other-tables WHERE <condition>; DELETE FROM orig_table USING other-tables WHERE <condition>; COMMIT; 

Now what if the amount of data is rather big, and the <condition> is expensive to compute? In PostgreSQL, a RULE or a stored procedure can be used to delete data on the fly, evaluating condition only once. Which solution is better? Are there other options?

Answer 1

[Expanding on dvv's answer]

You can move to an existing table as follows. For unmatched schema, you should specify columns.

WITH moved_rows AS (     DELETE FROM <original_table> a     USING <other_table> b     WHERE <condition>     RETURNING a.* -- or specify columns ) INSERT INTO <existing_table> --specify columns if necessary SELECT [DISTINCT] * FROM moved_rows; 

But you want to move the data into a new table (not an existing one), the outer syntax is different:

CREATE TABLE <new_table> AS WITH moved_rows AS (     DELETE FROM <original_table> a     USING <other_table> b     WHERE <condition>     RETURNING a.* -- or specify columns ) SELECT [DISTINCT] * FROM moved_rows;