I have a table like this

 id: int
 data: timestamp
 body: varchar

for some reason, I want to split this table into 2 separate tables:

 id: int
 data: timestamp

 id: int
 article_id: foreign key

I can easily achieve that after creating the article_body table by the following statement:

INSERT INTO article_body (article_id, body) 
select id, body
from articles;

and then drop body from articles and it's really good if you have a few rows. But in a special case when your table has many rows say 100 million rows it may take hours.

One way is to do that with some other program like python and run concurrent statements with offset, but I want to know is there a way to it faster inside SQL.

As I said in question I'm using PostgreSQL.

  • 2
    You might want to rethink the second table structure a bit - I would use the article_id column as both the FK and the PK - removing the id column from article_body .
    – jkavalik
    Oct 28, 2021 at 19:27
  • Yeah, It makes sense to make PK and FK the same column.
    – Mehdi
    Oct 28, 2021 at 19:30

1 Answer 1


One way to "batch" it might be something like:

INSERT INTO article_body (article_id, body) 
SELECT id, body
FROM articles
    SELECT 1 FROM article_body WHERE articles.id = article_body.article_id
LIMIT <batch_size>;

or some equivalent NOT IN() or LEFT JOIN .. WHERE article_id IS NULL.

You can run the query repeatedly anyway you want and it finds N rows that were not yet processed and transfers them. It spends some additional time on finding rows to process even with the right indexes, but you can throttle it the way you need, pause and resume anytime.

  • It might be easier to batch by articles.id, since it is numeric. Oct 29, 2021 at 2:36
  • 1
    @LaurenzAlbe I believe I do? Or do you mean the missing OREDR BY? I may be wrong but I did that on purpose to not trigger the order-by-limit optimization - I believe without it the planner can use different plans based on the ratio of processes/unprocessed rows instead of forcing the order and so scanning all the small IDs always.
    – jkavalik
    Oct 29, 2021 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.