Skip to content

Instantly share code, notes, and snippets.

@Olegas
Last active July 11, 2019 12:55
Show Gist options
  • Save Olegas/11f5bb8959ee9ca95061bb99f47e17ba to your computer and use it in GitHub Desktop.
Save Olegas/11f5bb8959ee9ca95061bb99f47e17ba to your computer and use it in GitHub Desktop.
Bulk insert

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    
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment