Import CSV into Postgres: Update & Insert at the same time

Question

So I´m fairly new to Postgresql and started working with it by testing out some stuff with pgadmin4 on Postgres9.6.

The problem:

I have a table: test(id, text)

In this table I have 10 rows of data. Now I want to import a CSV which has 12 rows to update the test table. Some text changed for the first 10 rows AND I want to insert the 2 additional rows from the CSV.

I know that you can truncate all the data from a table and just import everything again from the CSV, but that´s not a nice way to do this. I want to Update my existing data & Insert the new data with one query.

I already found a function which should solve this by using a temporary table. This updates the existing rows correct, but the 2 additional rows do not get inserted

CREATE OR REPLACE FUNCTION upsert_test(integer,varchar) RETURNS VOID AS $$
DECLARE
BEGIN
    UPDATE test
SET id = tmp_table.id,
text = tmp_table.text
FROM tmp_table
WHERE test.id = tmp_table.id;
IF NOT FOUND THEN
INSERT INTO test(id,text) values 
(tmp_table.id,tmp_table.text);
END IF;
END;
$$ Language 'plpgsql';

DO $$ BEGIN
PERFORM upsert_test(id,text) FROM test;
END $$;

So what do I need to change to also get the insert to work?

Unrelated, but: the language name is an identifier. Do not put it in single quotes use: language plpgsql — user330315
– user330315, Commented May 31, 2017 at 8:01

user330315 · Accepted Answer · 2017-05-31 07:49:25Z

7

Assuming you have a primary or unique constraint on the id column you can do this with a single statement, no function required:

insert into test (id, text)
select id, text
from tmp_table
on conflict (id) 
  do update set text = excluded.text;

answered May 31, 2017 at 7:49

user330315

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

3 Comments

StackAsk Over a year ago

Yeah I tried that first, but I always got the error: more than one row returned by a subquery

user330315 Over a year ago

@StackAsk: then you used a different query. The query in answer will not result in that error

StackAsk Over a year ago

Oh yes, my bad. Worked fine. Thank you!

Collectives™ on Stack Overflow

Import CSV into Postgres: Update & Insert at the same time

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related