CODEWITHSUNDEEP
postgresqlpostgresql-copy
Pavan
Pavan

Reputation: 41

How to ignore duplicate keys while using copy in postgresql

I am using COPY table_name FROM STDIN to import data. It is very efficient, but if there's any violation of duplicate keys, the whole procedure will be stopped. Is there anyway to around this?

why does not postgresql just give a warning and copy rest of the data?

Here's the example :

 select * from "Demo1";
 Id | Name  | Age 
 ---+-------+-----
  1 | abc   |  20
  2 | def   |  22


COPY "Demo1" from STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 3    pqr     25
>> 4    xyz     26
>> 5    abc     21
>> \.

ERROR:  duplicate key value violates unique constraint "Demo1_Name_key"
DETAIL:  Key ("Name")=(abc) already exists.
CONTEXT:  COPY Demo1, line 3

Here "Name" field is having unique constraint. Since string "abc" is already present in table. Its ignoring whole process.

Upvotes: 4

Views: 4100

Answers (1)

Robins Tharakan
Robins Tharakan

Reputation: 2473

You could use either of these two methods to import data:

  1. COPY FROM (to a temporary table). Weed out Primary-Key failures and import only valid data.
  2. Use FDW (like this example). Foreign-Data-Wrappers is recommended for Live Feeds / very large data sets, since you don't need to create an temporary copy (for errors / skip columns / skip rows etc.), and can directly run a SELECT statement skipping any column / row and INSERT into the destination table.

Upvotes: 1

Related Questions