From: gilberto.castillo@etecsa.cu
To: Michael Lewis <mlewis@entrata.com>
Cc: Pepe TD Vo <pepevo@yahoo.com>
Cc: Pgsql-admin <pgsql-admin@postgresql.org>
Cc: pgsql-general@postgresql.org
Subject: Re: [MASSMAIL]Re: partitioned table
Date: Thu, 09 Jan 2020 12:22:25 -0500
Message-ID: <647a17c086a0ae0490118eec4f5b46d3@etecsa.cu> (raw)
In-Reply-To: <CAHOFxGrp=T1QkBh6Lw-2zZqU=Lrno8kbnhwphc+cxKHFSZNaiQ@mail.gmail.com>
References: <148443228.5959267.1578589603211.ref@mail.yahoo.com>
<148443228.5959267.1578589603211@mail.yahoo.com>
<CAHOFxGrp=T1QkBh6Lw-2zZqU=Lrno8kbnhwphc+cxKHFSZNaiQ@mail.gmail.com>
I think you might create a block "Begin-Commit", before procedure call.
El 2020-01-09 12:15, Michael Lewis escribió:
>> when I manually mocking the data into both tables are fine and when
>> I run the procedure, I get errorcode: 42P10 MSG: there is no unique
>> or exclusion constraint matching on the CONFLICT specification
>>
>> the procedure is
>
> ...
>
>> INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id,
>> bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
>> bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id,
>> actn_tm_in_id, src_sys_id,
>> bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id,
>> uscis_emp_id, application_id,
>> rmtr_id, prpr_id, mig_filename)
>> SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id,
>> stg.bene_cntry_of_rsdc_id,
>> stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id,
>> stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,
>> stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id,
>> stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,
>>
>> stg.rmtr_id, stg.prpr_id, stg.mig_filename
>> FROM ecisdrdm.stg_bnft_curr_fact stg
>> ON CONFLICT ("bnft_fact_id") DO UPDATE
>> SET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
>
> The documentation and the error message explain the issue.
>
> "there is no unique or exclusion constraint matching on the CONFLICT
> specification"
>
> "The optional ON CONFLICT clause specifies an alternative action to
> raising a unique violation or exclusion constraint violation error."
>
> -https://www.postgresql.org/docs/current/sql-insert.html
>
> You have an index, but it is not unique. With partitioning, you cannot
> create a unique index on a column that is not contained by your
> partition key. So, you need to re-write to skip the use of ON CONFLICT
> I expect.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gilberto.castillo@etecsa.cu, mlewis@entrata.com, pepevo@yahoo.com, pgsql-general@postgresql.org
Subject: Re: [MASSMAIL]Re: partitioned table
In-Reply-To: <647a17c086a0ae0490118eec4f5b46d3@etecsa.cu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox