| 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: | Whole Thread | Raw Message | 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 |