CODEWITHSUNDEEP
postgresqlcsvpostgresql-9.6
StackAsk
StackAsk

Reputation: 121

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

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?

Upvotes: 1

Views: 1668

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 7

Related Questions