From: | Johann Kerdal <jkerdal(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Manage SCD 2 table using the INSERT --- ON CONFLICT |
Date: | 2016-02-15 09:54:16 |
Message-ID: | CAGoFJz5QLM3P0w3wtLuhLvBCjScCh6J5j-rP1Dc1ddgBN1gepA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2016-02-15 10:48:25 | Re: PosgreSQL Security Architecture |
Previous Message | David G. Johnston | 2016-02-15 06:10:33 | Re: four template0 databases after vacuum |