时间戳

首页 > 解决方案 > 如何在将批次插入表时提交循环内的每次迭代

sql - 如何在将批次插入表时提交循环内的每次迭代

问题描述

我编写了一个存储过程来从一个大表中选择记录块并分批插入到另一个表中。它似乎不是每次迭代都插入记录，而是在最后一次插入所有批次。有没有办法在每次迭代时将记录实际插入到目标表中？

这就是我的函数的样子：

CREATE OR REPLACE FUNCTION cms.load_records_from_staging_batch(batch_size int)
  RETURNS void
  LANGUAGE plpgsql
AS
$body$
DECLARE 
  row_cnt int;
  oset int;
  counter int;
BEGIN
  row_cnt := (select count(*)::int from cost_settlements_stg);
  raise notice 'Total % rows in cost_settlements_stg', row_cnt;
  oset := 0;
  while oset <= row_cnt loop
    insert into cms.cost_settlements 
      (item_text
      , item_description)
      select item_text
            , item_description
      from cms.cost_settlements_stg limit batch_size offset oset
    on conflict on constraint cost_settlements_unique_key 
    do nothing;
    
    oset := oset + batch_size;
    counter := counter + 1;
    raise notice 'Batch loaded %', counter';
  end loop;
END;

标签： sqlpostgresqlstored-proceduresoffsetbulkinsert

解决方案

The rows are inserted as you go, it is only that they are invisible until the end of the transaction.

Do all the inserts in a single transaction and in a single statement, that will be fast:

INSERT INTO cms.cost_settlements (item_text, item_description)
SELECT item_text, item_description
FROM cms.cost_settlements_stg
ON CONFLICT ON CONSTRAINT cost_settlements_unique_key DO NOTHING;

推荐阅读