How to ignore duplicate keys while using copy in postgresql

Question

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.

Import to an intermediate table and insert from there using the adequate rules. — Clodoaldo Neto
– Clodoaldo Neto, Commented Feb 3, 2016 at 10:21

Robins Tharakan · Accepted Answer · 2016-02-04 01:02:39Z

1

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

COPY FROM (to a temporary table). Weed out Primary-Key failures and import only valid data.
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.

answered Feb 4, 2016 at 1:02

Robins Tharakan

2,52122 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to ignore duplicate keys while using copy in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related