From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Johann Kerdal <jkerdal(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Manage SCD 2 table using the INSERT --- ON CONFLICT |
Date: | 2016-02-15 17:59:19 |
Message-ID: | 56C211F7.4060204@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Felipe Santos | 2016-02-15 18:00:04 | Re: Postgresql Server Upgarde |
Previous Message | Adrian Klaver | 2016-02-15 17:52:28 | Re: how do you determine if you have a healthy database |