I have table and partitioned for year year like this: CREATE TABLE ecisdrdm.bnft_curr_fact (bnft_fact_id numeric(38), bene_cntry_of_brth_id numeric(38), bene_cntry_of_rsdc_id numeric(38), bene_cntry_of_ctznshp_id numeric(38), frm_id numeric(38), svc_ctr_id numeric(38), actn_dt_in_id numeric(38), actn_tm_in_id numeric(38), src_sys_id numeric(38), bnft_hist_actn_id numeric(38), bene_id numeric(38), bene_end_dt_id numeric(38), petnr_app_id numeric(38), atty_id numeric(38), uscis_emp_id numeric(38), application_id numeric(38) default -1000000, rmtr_id numeric(38), prpr_id numeric(38), mig_filename varchar(80), mig_insert_dt timestamp, mig_modified_dt timestamp) partition by range (actn_dt_in_id)TABLESPACE ecisdrdm_data;
CREATE INDEX bnftn_fact_frmid_bmx1 ON ecisdrdm.bnft_curr_fact (frm_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_attyid_bmx1 ON ecisdrdm.bnft_curr_fact (atty_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_applicatiid_bti1 ON ecisdrdm.bnft_curr_fact (applicatiON_id)TABLESPACE ecisdrdm_index; CREATE INDEX src_sys_id_actn_dt_bmx1 ON ecisdrdm.bnft_curr_fact (src_sys_id, actn_dt_in_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_bnftfactid_bti1 ON ecisdrdm.bnft_curr_fact (bnft_fact_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_actndtinid_bmx1 ON ecisdrdm.bnft_curr_fact (actn_dt_in_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_coposit3_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id, uscis_emp_id)TABLESPACE ecisdrdm_index; CREATE INDEX src_sys_id_actn_dt_saa ON ecisdrdm.bnft_curr_fact (src_sys_id, actn_dt_in_id, applicatiON_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_beneid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_petnrappid_bti1 ON ecisdrdm.bnft_curr_fact (petnr_app_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_uscisempid_bmx1 ON ecisdrdm.bnft_curr_fact (uscis_emp_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_bnfhisactid_bmx1 ON ecisdrdm.bnft_curr_fact (bnft_hist_actn_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_src_sys_id_bmx1 ON ecisdrdm.bnft_curr_fact (src_sys_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_benenddtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_end_dt_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_prprid_bmx1 ON ecisdrdm.bnft_curr_fact (prpr_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_svcctrid_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id) TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_benctrysdcid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_rsdc_id) TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_benctrybrtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_brth_id) TABLESPACE ecisdrdm_index; as same as stg_bnft_curr_fact table, it's partitioned too.when I manually mocking the data into both tables are fine and when I run the procedure, I get errorcode: 42P10 MSG: thereis no unique or exclusion constraint matching on the CONFLICT specification the procedure is CREATE OR REPLACE FUNCTION ecisdrdm.pr_mig_stg_bnft_curr_fact( OUT v_ret text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE v_module text = 'pr_mig_stg_bnft_curr_fact '; host text = inet_server_addr(); errorcode text; errormsg text; errormsg_detail text; errormsg_hint text; BEGIN ------ MERGING: STG_BNFT_CURR_FACT into BNFT_CURR_FACT---- 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_filenameFROM ecisdrdm.stg_bnft_curr_fact stgON CONFLICT ("bnft_fact_id") DO UPDATE SET (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, mig_modified_dt)= (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, current_timestampFROM ecisdrdm.stg_bnft_curr_fact stgWHERE prod.application_id = stg.application_id); ---- -- Set return to "Success" for pr_merge_staging_tables function ---- v_ret := 'Success'; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS errormsg = MESSAGE_TEXT, errormsg_detail = PG_EXCEPTION_DETAIL, errormsg_hint = PG_EXCEPTION_HINT; errorcode := SQLSTATE; v_ret := concat('ERROR - FUNC: ' || v_module || ' ERRORCODE: ', errorcode, ' MSG: ' || errormsg || ' ', errormsg_detail || ' ', errormsg_hint); -- NOTE: Only writes to errorlog table if function is called directly -- If called through the pr_merge_staging_tables function the exception is raised and caught there instead PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, host, v_module, errorcode, v_ret ); end;$BODY$; when I select the table from pgadmin tool, it's not opened and popped up with a blank window with title "index out or range" I do have index on it. regards, Bach-Nga No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success