Hello,
I am trying to use the INSERT ON CONFLICT syntax to build an SCD 2 table loader.
here are the behaviors I need to achieve:
SCD 2 table: TAB_OUT
new candidate records: TAB_IN
The table TAB_IN contains the new candidate records to be submitted to TAB_OUT.
Case 1: The record presented by TAB_IN is already present in TAB_OUT and the fields were not modified
-> DO NOTHING
Case 2: The record presented by TAB_IN is not present in TAB_OUT
-> INSERT NEW RECORD (OK)
Case 3: The record presented by TAB_IN is already presente in TAB_OUT and the fields were modified
-> CLOSE existing record in TAB_OUT(OK)
-> INSERT new RECORD (KO)
I can't find a way to retrieve the list of records that were updated ON CONFLICT. I have tried the RETURNING but, it is retrieving the list of records processed either in the INSERT or the UPDATE.
Am I missing something or it is not possible with this syntax?
Thanks in advance
Johann