Bulk insert

bulk_insert.md

JavaSript (Node)

   const data = [{ field1: 1, field2: 'abc'}, {field1: 10, field3: 20}];
   driver.none(query, {
      data: JSON.stringify(data)
   });

SQL

   WITH raw_data AS (
      SELECT * FROM json_to_recordset(${data}::json) AS T(
             field1 integer,
             field2 string,
             field3 bigint,
             field4 integer
      )
   )
   , first_class_insert AS (
      INSERT INTO table (field1, field2, field3, field4)
      SELECT * FROM raw_data
       WHERE field1 IS NOT NULL
          ON CONFLICT (field1, field2, field3) 
          DO UPDATE SET field4 = EXCLUDED.field4
   RETURNING table.id
   )       
   , second_class_insert AS (
      INSERT INTO table (field1, field2, field3, field4)
      SELECT * FROM raw_data
       WHERE field1 IS NULL
          ON CONFLICT (field2, field3) 
          DO UPDATE SET field4 = EXCLUDED.field4
   RETURNING id
   )       
   SELECT * FROM first_class_insert
    UNION
   SELECT * FROM second_class_insert