Re: Manage SCD 2 table using the INSERT --- ON CONFLICT

On 02/15/2016 01:54 AM, Johann Kerdal wrote:
> 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.

I was with you until Case 3. Are you trying to UPDATE the existing
record with a closed flag and INSERT the new modified record?

>
> Am I missing something or it is not possible with this syntax?

What is the command you are using?
>
> Thanks in advance
>
> Johann
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com