0

I'm trying to bulk insert data from CSV into the PostgreSQL database using the COPY SQL command. Usually the CSV file contains minimum of 10 thousand rows to maximum of one million rows. This CSV file contains queryId as one of the columns and this queryId is the primary key of the table on which I'm trying to bulk insert. The CSV file may contain already inserted queryIds due to some reason. Is there any way I can bulk insert the CSV data by ignoring the unique key violation exception thrown by the database or can the exception be dealt with the COPY.

I've a naive approach which is make a list of all the queryIds present in the CSV file and query the database for the existing records that matches the queryId. Once I get the result delete those rows in the CSV which were already inserted in the database. But since the rows are in 100 thousand range I doubt this approach is feasible.

Note: Deleting the already existing queryId records in the database and then bulk inserting the CSV data is not what I'm looking for from the design point of view.

Akhil Vadrevu
  • 57
  • 8
  • Hi. Have you considered importing this csv file into an intermediate table and from there insert it into the final table using an upsert (e.g. `on conflict do nothing`)? – Jim Jones Sep 21 '20 at 12:02
  • 1
    No you cannot have `COPY` ignore or deal with the exceptions. See @JimJones suggestion. – Adrian Klaver Sep 21 '20 at 14:20

0 Answers0