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

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

Responses

Browse pgsql-general by date

  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