Postgresql – Bulk Inserts in Postgres

bulk-insertplpgsqlpostgresql

This is with respect to Data Migration activity where the historical data from the client database is migrated to vendor Postgres Database. There will be millions of transactions that need to be migrated as the Big Bang approach.

In oracle database, I used to use the below template of code for migration –

create or replace PROCEDURE PRC_TEST
AS
DECLARE
    CURSOR CUR
    IS
    SELECT ID,NAME  FROM test; 
TYPE test_typ IS TABLE OF CUR%ROWTYPE INDEX BY PLS_INTEGER;
test_tbl test_typ;  

BEGIN
OPEN CUR;
LOOP
FETCH cur BULK COLLECT INTO test_tbl LIMIT 1000;
DBMS_OUTPUT.PUT_LINE(test_tbl.COUNT);
     FORALL I IN 1..test_tbl.COUNT --SAVE EXCEPTIONS
                INSERT
                INTO test1(ID,NAME)
                VALUES
                (
                test_tbl(I).id,
                test_tbl(I).name
                );
                
       FORALL I IN 1..test_tbl.COUNT
                  UPDATE test1 
                  SET name     = name||test_tbl(I).NAME
                  WHERE id =test_tbl(I).id;
                  
                DBMS_OUTPUT.PUT_LINE('AFTER'|| test_tbl.COUNT);  
        EXIT WHEN CUR%NOTFOUND ;       
     END LOOP;
     commit;
 close cur;    
 EXCEPTION
      WHEN OTHERS THEN
        FOR I IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
        dbms_output.put_line('error'||sqlerrm);
        END LOOP;
END;
End PRC_TEST;

Is there a plpgsql equivalent code available for the same?
What approach to be used in Postgres while handling such migration activity ? please provide some best practices to be followed for better performance and handling/storing the error records in Postgres.

Thanks..!

Best Answer

I would convert this into a single INSERT statement using on conflict do deal with the "insert or update" part:

insert into test1(id, name)
select id, name 
from test
on conflict (id) do update
  set name = excluded.name;

This assumes that there is a unique index (or constraint) on test1.id

Related Question