How to use batch in upsert data in postgres sql?

Question

I have written an upsert query in postgres which upsert the data in to the table. Here I am trying to upsert ~ 5 to 10 million of rows.

INSERT INTO table_1 (col1, col2, col3)                      
(
    SELECT temp.col1 :: varchar, temp.col2 :: varchar, temp.col3 :: timestamp without time zone 
    
    FROM 
         (
            ('1', '1', '2021-03-02T09:16:54.358258'::timestamp),
            ('2', '2', '2021-03-02T09:16:54.358258'::timestamp)
         ) as temp

         (col1, col2, col3)
) ON CONFLICT (col1, col2) DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2, col3 = EXCLUDED.col3;

The query is working as expected.

But I want to use batch in the query.

How to use batch in the same query?

score 0 · Answer 1 · answered Mar 02 '21 at 14:43

I don't exactly understand "batch" in that context. But I think that the best thing you can do I to use COPY to a temp table and then use that to make INSERT ... ON CONFLICT statement. You can see example here: https://stackoverflow.com/a/49836011/1958544

How to use batch in upsert data in postgres sql?

1 Answers1