Update instead inserting same primary key when import using copy query to postgresql using pgadmin4

Question

As the title say, i have table food with field food_id(PK) and food_name. I already have this data on table

food_id|food_name
-----------------
0000001|food1
0000002|food2
0000003|food3

and on my csv

0000001|apple
0000004|banana
0000005|grape

this is my query if there is no duplicate PK

 copy foodfrom 'd:\testingfood.csv' delimiter '|' csv

but i want to update the food1 to apple to apple and insert 0000004|banana and 0000005|grape?

Is it possible?

Answer 1

You cannot do that in a single COPY command. Use a temporary table and INSERT with ON CONFLICT, e.g.:

create temp table tmp_food (like food); -- create temporary table like food

copy tmp_food from 'd:\testingfood.csv' delimiter '|' csv;  -- copy to temp table

insert into food (food_id, food_name)   -- insert into food from temp table
select food_id, food_name
from tmp_food
on conflict (food_id) do                -- update instead of error
update set food_name = excluded.food_name;

drop table tmp_food;                    -- drop temp table