Re: [MASSMAIL]Re: partitioned table

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.