sql - 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;