move data from one table to another, postgresql edition

Question

Hi All,

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

You might dump the table data to a file, then insert it to another table using COPY Usually COPY is faster than INSERT.

Answer 2

If the condition is so complicated that you don't want to execute it twice (which BTW sounds unlikely to me, but anyway), one possibility would be to ALTER TABLE ... ADD COLUMN on the original table to add a boolean field, and run an UPDATE on the table to set that field to true WHERE <condition>. Then your INSERT and DELETE commands can simply check this column for their WHERE clauses.

Don't forget to delete the column from both source and destination tables afterwards!

Hmm, even less intrusive would be to create a new temporary table whose only purpose is to contain the PKs of records that you want to include. First INSERT to this table to "define" the set of rows to operate on, and then join with this table for the table-copying INSERT and DELETE. These joins will be fast since table PKs are indexed.

---

[EDIT] Scott Bailey's suggestion in the comments is obviously the right way to do this, wish I'd thought of it myself! Assuming all the original table's PK fields will be present in the destination table, there's no need for a temporary table -- just use the complex WHERE conditions to insert into the destination, then DELETE from the original table by joining to this table. I feel stupid for suggesting a separate table now! :)