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
