-1

During an UPSERT query on PostgreSQL it is not obvious which entries of a table has changed (inserted or updated).

Query:

First add an unique constraint for the businessKey:

alter table public."test_table" add constraint "bk_constraint" unique ("businessKey");

INSERT ... ON CONFLICT ... DO UPDATE

INSERT INTO public."test_table" ("first_name","last_name","role", "businessKey") 
SELECT 
UNNEST(ARRAY['Jon','Anna','Bernhard']),
UNNEST(ARRAY['Doe','Miller','Hill']),
UNNEST(ARRAY['team member','team member','project lead']),
UNNEST(ARRAY['Jon JD','Anna AM','Bernhard AH'])
ON CONFLICT ("businessKey") DO UPDATE 
SET "first_name"=EXCLUDED."first_name","last_name"=EXCLUDED."last_name""role"=EXCLUDED."role","businessKey"=EXCLUDED."businessKey";

Now I want to know which entries were inserted or updated for further processing. How can I get this information?

leon22
  • 5,280
  • 19
  • 62
  • 100

1 Answers1

0

just add an update statement to record this information:

...
ON CONFLICT ("businessKey") DO UPDATE 
SET is_updated = true,  
 "first_name"=EXCLUDED."first_name",
 "last_name"=EXCLUDED."last_name"...
JGH
  • 15,928
  • 4
  • 31
  • 48
  • Could you please explain this a little bit?! I need a returned array with the businessKey's from the DO UPDATE process. (not only true or false) => Somehow I should return EXCLUDED."businessKey"?! And btw. this code doesn't work: ERROR: column "is_updated" of relation "test_table" does not exist. – leon22 Apr 13 '21 at 14:01
  • Postgres does not distinguish the updated rows from the inserted rows. Normally getting this data is just not worth the effort - what do you intend on doing with it? If you need this them you will need separate statements: Update ... set ... where exists (select ...) followed by insert(...) select ... where not exists ( select ...), where the select after exists is the same in each case. Perhaps if you explain the reasoning for this the community can help with another solution. – Belayer Apr 14 '21 at 23:34
  • Setting an is_updated flag works only where there is a column "is_updated boolean" in the table. Then to know if this particular statement updated if you would have to set it False for every row is prior to the Upsert. – Belayer Apr 14 '21 at 23:35