0

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?

Alexander Chandra
  • 587
  • 2
  • 9
  • 23

1 Answers1

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
klin
  • 112,967
  • 15
  • 204
  • 232
  • works great, thank you... But there are one thing that i want to ask you. What the difference between temp table and ordinary table? since you drop it anyway. – Alexander Chandra Feb 22 '17 at 02:32
  • Temporary tables are maintained in memory and if available memory buffers are sufficient, are never written to disk. Small temporary tables are hence more efficient than regular ones. Read also [this post](http://stackoverflow.com/q/486154/1995738). One more advantage - they are not seen by other users. – klin Feb 22 '17 at 02:41
  • i know this i a bit unrelated, but how do i set the path (d:\testingfood.csv) using php open file dialog or input type file? because browser cant get full path for some security reason – Alexander Chandra Mar 01 '17 at 03:15
  • I'm not sure what your problem is but take into account that the `copy` command concerns server side file system, so the path should be an absolute path on server. The user who owns Postgres (typically `postgres`) must have read access to the file. – klin Mar 01 '17 at 08:43