I have connected express js with postgress sql.
I am building small file upload project.
I have one lakh csv data to upload and I have to do it fast.
I have to insure that user_email is unique and store the recent value during insertion.
I did it with two approach;
// console.log(parsedData)
// let q = “INSERT INTO employee (user_name, user_email, age, address) VALUES ($1, $2, $3, $4) ON CONFLICT (user_email) DO UPDATE SET user_name = EXCLUDED.user_name, age = EXCLUDED.age, address = EXCLUDED.address;”;

  // values.forEach(element => {
  //   // console.log(element)
  //   client.query(
  //    q,
  //     [element[0],element[1],element[2],element[3]],
  //     (err, result) => {
  //       if (err == null) {
  //         console.log(result.rowCount + " rows inserted successfully");
  //         values=[]
          
  //       } else {
  //         console.log(err);
  //       }
  //     }
  //   );
  // });

This code iterates over each row and work fines. but the problem is that it takes 2-3 mins to insert all data.

approach 2:
client.query(
format(“INSERT INTO employee VALUES %L ON CONFLICT (user_email) DO UPDATE SET user_name = EXCLUDED.user_name, age = EXCLUDED.age, address = EXCLUDED.address;”, values),
[],
(err, result) => {
if (err == null) {
console.log(result.rowCount + ” rows inserted successfully”);
values=[]

      } else {
        console.log(err);
      }
    }
  );

sample data:
values= [[
‘Joyce West’,
‘[email protected]’,
’99’,
‘989 Joshua LakenNew Amber, CO 85650’
],
[
‘Jon Santana’,
‘[email protected]’,
’41’,
‘9974 Burns TurnpikenPort Donald, AK 79911’
]]

parsedData=[ {
name: ‘Tara Miller’,
email: ‘[email protected]’,
age: ’49’,
address: ‘Unit 6011 Box 5872nDPO AE 90613’
},
{
name: ‘Derrick Whitney’,
email: ‘[email protected]’,
age: ’55’,
address: ‘070 Gerald Manor Suite 322nEast Sarah, NJ 09879’
}]

In approach two I inserted all data in single query and it is extremeley fast. but the problem with this is that it show me the error:
ON CONFLICT DO UPDATE command cannot affect row a second time

I am looking something like “on duplicate key update” in postgress
please suggest something to maintain speed of upload as well as solve duplicate problem

I am expecting a solution to this answer. And please mention the query as well as update in express file