MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Date: Thu, 09 Jan 2020 12:22:25 -0500
From: gilberto.castillo@etecsa.cu
To: Michael Lewis <mlewis@entrata.com>
Cc: Pepe TD Vo <pepevo@yahoo.com>, Pgsql-admin <pgsql-admin@postgresql.org>,
 pgsql-general@postgresql.org
Subject: Re: [MASSMAIL]Re: partitioned table
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>
Message-ID: <647a17c086a0ae0490118eec4f5b46d3@etecsa.cu>
User-Agent: Roundcube Webmail/1.2.5
Precedence: bulk

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.