Postgres ERRORR :: while copying (batching) data from one table to another with same schema and delete from original table

0
votes

I have a table 'table1' with columns (id , createddate). Assume I have millions of records in it and want to copy all records older than today to 'table1_back' with same schema as table1.

I tried to run this in batch as follows but its throwing error 

WITH row_batch AS (
    SELECT id 
    FROM table1 
    WHERE createddate::date < NOW() LIMIT 20000 ), 
delete_rows AS (
    DELETE FROM table1 t 
    USING row_batch b 
    WHERE t.id = b.id RETURNING *)
INSERT INTO table1_back SELECT * FROM delete_rows;

But am getting error ERROR: INSERT has more expressions than target columns but actually its exact replica of table1.

1
postgresql

1 Answers

1
votes

I guess, you should try

RETURNING t.*

Error happens because columns from row_batch subquery are added to delete_rows subquery.