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

Reply via email to