Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ipbGm-0003c2-56 for pgsql-admin@arkaria.postgresql.org; Thu, 09 Jan 2020 17:06:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ipbGk-0005jj-Ue for pgsql-admin@arkaria.postgresql.org; Thu, 09 Jan 2020 17:06:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ipbGk-0005jX-0W for pgsql-admin@lists.postgresql.org; Thu, 09 Jan 2020 17:06:50 +0000 Received: from sonic313-14.consmr.mail.bf2.yahoo.com ([74.6.133.124]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ipbGf-0000XQ-V1 for pgsql-admin@postgresql.org; Thu, 09 Jan 2020 17:06:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1578589604; bh=LVVO2SJmHoTQ4zhxGLVrjUWvWEyD+5JiaVfEMKVka28=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=et12oE40CfRLDj/7ZWucc2CNH1CLyvDYqsd2OLnlLA70PnBNLG4OF+24Sq2ZkWz5ohfpKEY+YUi7cjl4YrPoi2BFJEzGa8dRMKLW+zOcn6sJN02TJ3WrBGtyxcdt/iUA2G2txmvX/XIfmstwEANyBDIIBwLcH3AoMN8Jslg7DwYup66eg61rM4lMtUyLtor6DaXHAnwKfW6OWEsisUYc35PPTo9q3pYdhtt8zB7bwlrN16WV1V3vdu+Gk4rZr0WjP22IPgvp+Nuu4ZRwrKz93Ei2REsYLDiPnugXq8tz9jEi1DCnlKhynpATkwbRo+VtSfvFtZEbXylyZWitMc8MXA== X-YMail-OSG: Kv1d1loVM1lRIb5j6gt9wKWa6S8nIhYibwLbUnkZ48S1PJse1jxuCwRwGZ50aBu PE20U4M26dv1uWuHHlohHWoUrHX8VyGdS1xXnZ6WKIdQMxVqnaCXUQ9OHCQX0Av.0tV6PRRnkUv_ pjAifw5Tk.AThhrsin98GWh2MDRFFfeboHw8fRWFlNFo1IaK17CQD_ksaqrEA2y1.M3qGNckOKU5 0U6XQYsMJF9toc8ThLJRG.PdcEuGBX1eBrC8AG_MZSl039RC6hWyTaACpCU2JWsPMuLO3NJYMatZ Q9lsljJAcQ.Z2_jZgGvhDKnxqQI9v0VoT_fEfuYHM71A0U8p.h6D_dg76yTOBF8rmRDF.gHDXyLD 3Lz_VwfoJ02dHhA5vRgEWwzvAcOwKMB6Ux9s3xFsVCiYMjmpDPOXT4xuke7DO9ieXS1Gn5swtnn8 6WPi923n_JSaap4PkvNRufoeNq4jKUQ9d8zsVrZav9HUnz8hfRsitYupaXsmO3T4_R9929Rh9eHe hKt3jgfSZVBb2OuLtQVxgclKd8t66nWliKDScVJWl.LlIgj9J7NvT9gk0Pkeaw1XyVF__02T0aFa sGXH0OSMauAXLwCFtPoSkbzDX_muiUe5MEQE8M7yQaS2BYWNubyfsiU7W6K.7RKCOGjuUXz1z06J EEXVVcUBu6A074_.XVvtohCeOY5IVBTTznCeu6476uxCAyn2uRWDlGg9nByKcJTs0Bh0IR5WoR5n t6tjaGJ_w0SIfOqgJrb6Vy05mvIBlQ1R1OpdC9fHJx8aea5jQ1pVHys2cK1QBZc0CXYvhNxWQcHV 9UeBW0Dgajr_1ol7Pqxb4Sw13RdeV2K0zQi2LOk1MWMpjZ0p1RFKPKe9tfL5WmUJeFKy157IpKDX Yr1mFpwRPG_3_YdVovQVO83_R0h_8b3Tj.NpilbSHnOuFJ27R4Kh.lE8j2MF_7di_Wd6zeoqufs0 LYNBJQgS9dHfRXzEO4liXyG21DuDLk2Rj4nDB3rF8WdoHvkzQ2ooCBHj.2B.ycJLEdkN57oAGf2o xYnBduoVCwCNxiLKz2YQhZFF4zyjdhD4cwuTaY3BFh7OzHQqOX10WfYwkIp_lmAQ9yDnmn4xLAIg _rnBLpnBJDXv1_1xE6E7ce4XmqbkJhNtSsrxoeUw_NKBzjH3rQ11s5fYdXr1KR4_GAda4EQ25PzV _EbnxI_AF_T_ZuOUJjXet7hOEDd1oW2dpWCHqAptLs8WCKfHxzcI197ORIVGF_rHpeOHSybI9tVx 9659ht1MCWpinetKnYjT3wP_mLsbHZ9xAs_6UINQdfguU7YBjDQkvfHamT5u7HP9zk2rYfVnGpae ftNWhDE3wO__zjd.KS3baYJB5DDvREHGv_5DC0DNexQE- Received: from sonic.gate.mail.ne1.yahoo.com by sonic313.consmr.mail.bf2.yahoo.com with HTTP; Thu, 9 Jan 2020 17:06:43 +0000 Date: Thu, 9 Jan 2020 17:06:43 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin , "pgsql-general@postgresql.org" Message-ID: <148443228.5959267.1578589603211@mail.yahoo.com> Subject: partitioned table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5959266_2103167348.1578589603206" References: <148443228.5959267.1578589603211.ref@mail.yahoo.com> X-Mailer: WebService/1.1.14873 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.79 Safari/537.36 Content-Length: 23426 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5959266_2103167348.1578589603206 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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=C2=A0 numeric(38), bene_cntry_of_rsdc_id numeric(38), bene_cnt= ry_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 numer= ic(38), bnft_hist_actn_id numeric(38), bene_id numeric(38), bene_end_dt_i= d numeric(38), petnr_app_id numeric(38), atty_id numeric(38), uscis_emp_= id numeric(38), application_id numeric(38) default -1000000, rmtr_id num= eric(38), prpr_id numeric(38), mig_filename varchar(80), mig_insert_dt t= imestamp, mig_modified_dt timestamp) partition by range (actn_dt_in_id)TAB= LESPACE ecisdrdm_data; CREATE INDEX bnftn_fact_frmid_bmx1 ON ecisdrdm.bnft_curr_fact (frm_id)TABLE= SPACE ecisdrdm_index; CREATE INDEX bnftn_fact_attyid_bmx1 ON ecisdrdm.bnft_curr_fact (atty_id)TAB= LESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_applicatiid_bti1 ON ecisdrdm.bnft_curr_fact (applic= atiON_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_fa= ct_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_i= d, 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)TAB= LESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_petnrappid_bti1 ON ecisdrdm.bnft_curr_fact (petnr_a= pp_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_uscisempid_bmx1 ON ecisdrdm.bnft_curr_fact (uscis_e= mp_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_bnfhisactid_bmx1 ON ecisdrdm.bnft_curr_fact (bnft_h= ist_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_en= d_dt_id)TABLESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_prprid_bmx1 ON ecisdrdm.bnft_curr_fact (prpr_id)TAB= LESPACE ecisdrdm_index; CREATE INDEX bnftn_fact_svcctrid_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_i= d) 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 m= ocking the data into both tables are fine and when I run the procedure, I g= et errorcode: 42P10 MSG: thereis no unique or exclusion constraint matching= on the CONFLICT specification the procedure is=C2=A0 CREATE OR REPLACE FUNCTION ecisdrdm.pr_mig_stg_bnft_curr_fact( OUT v_ret te= xt)=C2=A0 =C2=A0 RETURNS text=C2=A0 =C2=A0 LANGUAGE 'plpgsql' =C2=A0 =C2=A0 COST 100=C2=A0 =C2=A0 VOLATILE=C2=A0AS $BODY$=C2=A0DECLARE=C2= =A0 v_module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text =3D 'pr_mig_stg_b= nft_curr_fact '; host text =3D inet_server_addr(); errorcode=C2=A0 =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0text; errormsg =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0text; errormsg_detail=C2=A0 text;=C2=A0 = =C2=A0 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_br= th_id, bene_cntry_of_rsdc_id,=C2=A0 bene_cntry_of_ctznshp_id, frm_id, svc_c= tr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,=C2=A0 bnft_hist_actn_id, b= ene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id= ,=C2=A0 rmtr_id, prpr_id, mig_filename)SELECT stg.bnft_fact_id, stg.bene_cn= try_of_brth_id, stg.bene_cntry_of_rsdc_id,=C2=A0 stg.bene_cntry_of_ctznshp_= id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.s= rc_sys_id,=C2=A0 stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, st= g.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,=C2=A0 st= g.rmtr_id, stg.prpr_id, stg.mig_filenameFROM ecisdrdm.stg_bnft_curr_fact st= gON CONFLICT ("bnft_fact_id") DO UPDATE=C2=A0SET (bnft_fact_id, bene_cntry_= of_brth_id, bene_cntry_of_rsdc_id,=C2=A0 bene_cntry_of_ctznshp_id, frm_id, = svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,=C2=A0 bnft_hist_actn_= id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, applicati= on_id,=C2=A0 rmtr_id, prpr_id, mig_filename, mig_modified_dt)=3D=C2=A0(SELE= CT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,= =C2=A0 stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_d= t_in_id, stg.actn_tm_in_id, stg.src_sys_id,=C2=A0 stg.bnft_hist_actn_id, st= g.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp= _id, stg.application_id,=C2=A0 stg.rmtr_id, stg.prpr_id, stg.mig_filename, = current_timestampFROM ecisdrdm.stg_bnft_curr_fact stgWHERE prod.application= _id =3D stg.application_id); =C2=A0 =C2=A0----=C2=A0 =C2=A0-- Set return to "Success" for pr_merge_stagi= ng_tables function=C2=A0 =C2=A0---- v_ret :=3D 'Success';=C2=A0=C2=A0 EXCEPTION WHEN OTHERS THEN=C2=A0 GET STACKED DIAGNOSTICS=C2=A0 errormsg = =3D MESSAGE_TEXT,=C2=A0 =C2=A0 =C2=A0 =C2=A0 errormsg_detail =3D PG_EXCEPTI= ON_DETAIL,=C2=A0 =C2=A0 =C2=A0 =C2=A0 errormsg_hint =3D PG_EXCEPTION_HINT;= =C2=A0=C2=A0 errorcode :=3D SQLSTATE;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ret :=3D= concat('ERROR - FUNC: ' || v_module || ' ERRORCODE: ', errorcode, ' MSG: '= || errormsg || ' ', errormsg_detail || ' ', errormsg_hint); -- NOTE: Only writes to errorlog table if function is called directly -- I= f called through the pr_merge_staging_tables function the exception is rais= ed and caught there instead PERFORM ecisdrdm.pr_write_error_log( CURRENT_US= ER, host, v_module, errorcode, v_ret ); end;$BODY$; when I select the table from pgadmin tool, it's not opened and popped up wi= th 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.=C2=A0 If you avoid people for the= ir 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 t= o 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 ------=_Part_5959266_2103167348.1578589603206 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I have table and partitioned for year year like= this:

CREATE TABLE ecisdrdm.bnft_curr_fac= t
=09(bnft_fact_id = =09=09=09 numeric(38),
=
=09bene_cntry_of_brth_id=   numeric(38),
=09bene_cntry_of_rsdc= _id =09 numeric(38),
=09bene_cntry_of_ctznshp_i= d =09 numeric(38),
=09frm_id =09=09=09=09 numeric(38),
=09svc_ctr_id =09=09=09 numeric(38),
=09actn_dt_in_id =09=09=09 numeric(38),
=09actn_tm_in_id = =09=09=09 numeric(38),
=09src_sys_id =09=09=09 = numeric(38),
=09bnft_hist_actn_id =09=09 numeric(38),
=09b= ene_id =09=09=09=09 numeric(= 38),
=09bene_end_dt= _id =09=09 numeric(38),
=09petnr_app_id =09=09=09 numeric(38),
=09atty_id =09=09=09=09 numeric(38),
=09uscis_emp_id =09=09=09 numeric(38),
=09application_id =09=09=09 numeric(38) default -1000000,
=09rmtr_id =09=09=09=09 numeric(38),
=09prpr_id =09=09=09=09 numeric(38),
=09mig_filename = =09=09=09 varchar(80),
=09mig_insert_dt =09=09= =09 timestamp,
=09= mig_modified_dt =09=09 timestamp
) partition by range (actn_dt_in_id)
TABLES= PACE ecisdrdm_data;


CREATE INDEX bn= ftn_fact_frmid_bmx1 ON ecisdrdm.bnft_curr_fact (frm_id)
TABLESPAC= E ecisdrdm_index;

CREATE INDEX bnftn_fact_attyid_b= mx1 ON ecisdrdm.bnft_curr_fact (atty_id)
TABLESPACE ecisdrdm_inde= x;

CREATE INDEX bnftn_fact_applicatiid_bti1 ON eci= sdrdm.bnft_curr_fact (applicatiON_id)
TABLESPACE ecisdrdm_index;<= /div>

CREATE INDEX src_sys_id_actn_dt_bmx1 ON ecisdrdm.b= nft_curr_fact (src_sys_id, actn_dt_in_id)
TABLESPACE ecisdrdm_ind= ex;

CREATE INDEX bnftn_fact_bnftfactid_bti1 ON eci= sdrdm.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_cu= rr_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_bmx= 1 ON ecisdrdm.bnft_curr_fact (bene_id)
TABLESPACE ecisdrdm_index;=

CREATE INDEX bnftn_fact_petnrappid_bti1 ON ecisdr= dm.bnft_curr_fact (petnr_app_id)
TABLESPACE ecisdrdm_index;
=

CREATE INDEX bnftn_fact_uscisempid_bmx1 ON ecisdrdm.bnf= t_curr_fact (uscis_emp_id)
TABLESPACE ecisdrdm_index;
<= br>
CREATE INDEX bnftn_fact_bnfhisactid_bmx1 ON ecisdrdm.bnft_cur= r_fact (bnft_hist_actn_id)
TABLESPACE ecisdrdm_index;
<= br>
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 (b= ene_end_dt_id)
TABLESPACE ecisdrdm_index;

CREATE INDEX bnftn_fact_prprid_bmx1 ON ecisdrdm.bnft_curr_fact (prpr_id)<= /div>
TABLESPACE ecisdrdm_index;

CREATE INDEX = bnftn_fact_svcctrid_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id)
<= div>TABLESPACE ecisdrdm_index;

CREATE INDEX bnftn_= fact_benctrysdcid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_rsdc_id)
TABLESPACE ecisdrdm_index;

CREATE IND= EX bnftn_fact_benctrybrtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_b= rth_id)
TABLESPACE ecisdrdm_index;

=
as same as stg_bnft_curr_fact tabl= e, it's partitioned too.
whe= n 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 
CREATE OR REPLACE= FUNCTION ecisdrdm.pr_mig_stg_bnft_curr_fact(
=09OUT v_ret text)
    RETUR= NS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS= $BODY$ 
DECLARE 
=09v_module           text= =3D 'pr_mig_stg_bnft_curr_fact ';
=09host=09=09=09 text =3D inet_server_addr();
=09errorcode         =   text;
=09errormsg =09=09   &= nbsp;   text;
=09errormsg_detail  text;
    errormsg_hint=09 text;
=09
BEGIN

----
-- MERGING: STG_BNFT_CURR_FACT into BNFT_CURR_FACT
----
<= div>
INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_i= d, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id, 
=09bene_cntry_of_ctznshp_id, frm_id, sv= c_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id, 
=09bnft_hist_actn_id, bene_id, bene_= end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id, 
=
=09rmtr_id, prpr_id, mig= _filename)
SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, st= g.bene_cntry_of_rsdc_id, 
=09stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, st= g.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id, 
=09stg.bnft_hist_actn_id, stg.bene_i= d, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg= .application_id, 
=09= stg.rmtr_id, stg.prpr_id, stg.mig_filename
FROM ecisdrdm.s= tg_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= , 
=09bene_cnt= ry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys= _id, 
=09bnft_= hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id,= application_id, 
=09= rmtr_id, prpr_id, mig_filename, mig_modified_dt)
=3D =
(SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cn= try_of_rsdc_id, 
=09<= /span>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, 
=09stg.bnft_hist_actn_id, stg.bene_id, stg.be= ne_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.applicat= ion_id, 
=09st= g.rmtr_id, stg.prpr_id, stg.mig_filename, current_timestamp
FROM = ecisdrdm.stg_bnft_curr_fact stg
WHERE prod.application_id =3D stg= .application_id
);

   ----
   -- Set return to "Success" for pr_merge_staging_tables = function
   ----
=09v_ret :=3D 'Success';  

=
EXCEPTION
=09W= HEN OTHERS THEN
  =09= GET STACKED DIAGNOSTICS 
=09=09errormsg =3D MESSAGE_TEXT,
    =     errormsg_detail =3D PG_EXCEPTION_DETAIL,
  &nb= sp;     errormsg_hint =3D PG_EXCEPTION_HINT;  
=09=09errorcode :=3D SQLSTA= TE;
        v_ret :=3D concat('ERROR - FUNC: = ' || v_module || ' ERRORCODE: ', errorcode, ' MSG: ' || errormsg || ' ', er= rormsg_detail || ' ', errormsg_hint);

=09-- NOTE: Only writes to errorlog tabl= e if function is called directly
=09-- If called through the pr_merge_staging_tables function = the exception is raised and caught there instead
=09PERFORM ecisdrdm.pr_write_error_log( CURRE= NT_USER, host, v_module, errorcode, v_ret );

end;<= /div>
$BODY$;


when I selec= t the table from pgadmin tool, it's not opened and popped up with a blank w= indow with title "index out or range" I do have index on it.<= /div>

<= span style=3D"font-size: 13.44px;">regards,


=
Bach-Nga

No one in this world is pure and perfect.&nbs= p; If you avoid people for their mistakes you will be alone. So judge less,= love and forgive more.
To call him a do= g hardly seems to do him justice though in as much as he had four legs, a t= ail, and barked, I admit he was, to all outward appearances. But to those w= ho knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love = generously **Care deeply **Speak kindly.
**= * Genuinely rich *** Faithful talent *** Sharing success
<= /div>
------=_Part_5959266_2103167348.1578589603206--