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 1gtHDD-0004k6-SI for pgsql-admin@arkaria.postgresql.org; Mon, 11 Feb 2019 19:25: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 1gtHDB-0003sr-PI for pgsql-admin@arkaria.postgresql.org; Mon, 11 Feb 2019 19:25:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtHDB-0003sQ-GI for pgsql-admin@lists.postgresql.org; Mon, 11 Feb 2019 19:25:49 +0000 Received: from sonic305-3.consmr.mail.bf2.yahoo.com ([74.6.133.42]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtHD8-0004ZN-JX for pgsql-admin@lists.postgresql.org; Mon, 11 Feb 2019 19:25:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1549913143; bh=o77uDN/Fe0EUjolVEp0eRfXAzRGMmH4KBikq7kTG2GI=; h=Date:From:Reply-To:To:Cc:Subject:References:From:Subject; b=D5AMKCrDElBitP1r5ZW3kcWim+7fEJXKlbYzOOXvkkEeSHDYFmzxvhdgc99wQh9NiPt8g1gtqeA2ELOWv7Kaz5nOOJJCcAKonZjAmtAF0gDnx1VLWwGn7YBGjXODrIxHhjQkiiFNExi19Zw2r8B0z101ouhKj6223Zni+ft1bIvFZpVWcrlXcp3XBLoR3WTtXdeGeSFFEPDssQU60DK16+gmURKic0ZNwU67J3O3ZHw+J0xf8PWi6pisPzzY+WSV2bwGqJrQVwD6Yq+70OdK1nzPrrmFRVrU+QcGE35vC+tZ8A7+/mPFTLFHPMtmLI03rj9of+9jqagf0vTFVrCijQ== X-YMail-OSG: 4UNNAvkVM1lRcFE.2AlFUzsj4fl6pOphFaqNYjGw1yUnidYrodRdhGNe4SZLeCg TjTjmcLNqBQemjdHMoK6sMoRRbQXnF92I79X9HLIjY00oCtcDhaa_cOzjnYMHJWH1dyBMfR7qZyR IT4vkyh2cZgLeVXGE7gUy7i.X0ISd3DVPyjyzMm52IafQqq9qSDOI35HmErfiHo1rodnSBDT1xBm RZxCR55CSV.vrhO69p11b.wdWxtlA6bq6zoJCgP8M3k3i2sOZT8lI.obHyx7Lp5g4GxYlzX47FuJ zTirhAIH9oJQ3mpYVUD7cOO7H2jGV9Ub9YVTWL35tZ3XuJaQqezleji3RCl4_3ABNwIi56RsUc0y bFD3r384Up75cKMCejZbXw5dZ2QIBUtsZBPGMeYy6fbSAJr11DZKZFJUTSCFLeZiueVrQv6N9VD8 cGs08TBlk98dC8I8b.DhBNhzGxH697oUQjM14h4yeiF_yUuCFwRF7mAuhRnCSlSYGftlnkb6gMMl XGB56p9kgA_u6B4B3KAfuHA814E8GyINPIXesB92DO63FU2RqE3EvntFuwJxDMGpgLkrD83kMMcp 5n6t6wEKwVvEAKmBzl7psfyXCJyysAw7GUmcmS3CHqk.e4T5v_3x3RBac5m9gTqGAqZwtsh614FS XQah.wt52pPEYyVFq_KQ0alPWhO1JVAQBGjlHii5Z5rvacLQFRBRNxrxqOSg81Dk3IaKnvMLz6pv O7svZaAlFUHE2fajrqiKqtxFQXA9sXom_OhIzEOlZKNR80Hhe63WYmvBGz9_6gLxPxiibnbhButo zHAKayUJsIAvXKbPEyscO768jnQjqsO6zGp_jKJ93Zj3W5q7YRpgmWbfme0R6.fgn9wZqYtDq_UH ToAsbrk2wZ7JhpiMEz4XL9wJWRQeJcgvA4MFr5875W.kybtz6BZ0IFNijJJSSeWIi6eA4SgLXQ_6 syIMUn7B0BW9AQ2ZcFo99Y31ZsiKaLFTKicwWq6dEd9t8R.qvQ2urddJPXxCrxiIh4KqzE21lAVy E169JIfp6BwpDCSBs43MLH9wtkXUuyzGgyNALyTV8WU_UdEnbBXMoi_heAvPmk4vQv_gs Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.bf2.yahoo.com with HTTP; Mon, 11 Feb 2019 19:25:43 +0000 Date: Mon, 11 Feb 2019 19:25:42 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: "David G. Johnston" Cc: Pgsql-admin Message-ID: <2112371589.1866229.1549913142122@mail.yahoo.com> Subject: insert data with invalid input syntax for integer:"42P01" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1866228_495162720.1549913142120" References: <2112371589.1866229.1549913142122.ref@mail.yahoo.com> X-Mailer: WebService/1.1.13027 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36 Content-Length: 15093 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1866228_495162720.1549913142120 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, I am still getting=C2=A0ERROR: invalid input syntax for integer:"42P01" whe= n I am insert a data into the table.CONTEXT: PL/pgSQL function cidr_staging= .trigger_fct_tr_stg_adjudicative_status_ins() line 29 at assignment.Insert = query:insert into cidr_staging.stg_adjudicative_status (receipt_number, adj= udicative_action_date, adjudicative_time_stamp, adjudicative_status, mig_fi= lename, mig_insert_dt, mig_modified_dt, mig_seq) values (E'LIN1890030316', = E'20180409',E'0752',E'Dennied',E'c3_20180626_adjudicativestatus_000.txt',NU= LL,NULL,172011);the trigger is:Create or replace function cidr_staging.trig= ger_fct_tr_stg_adjudicative_status_in() RETURNS trigger AS $BODY$declarev_s= eq bigint:=3D0;v_ErrorCode bigint;V_ErrorMsg varchar(512)'v_Module varchar(= 32):=3D 'TR_STG_ADJUDICATIVE_STATUS_INS';BEGINBEGINselect nextval('sq_stagi= ng') into STRICT v_seq;if NEW.mig_seg is null then=C2=A0 =C2=A0NEW.mig_seq:= =3Dv_seq;enf if;if NEW.mig_filename is null then=C2=A0 =C2=A0NEW.mig_filena= me :=3D'Unknown';end if;exceptionwhen others then=C2=A0 =C2=A0v_ErrorCode := =3D SQLSTATE'=C2=A0 =C2=A0v_ErrorMsg :=3D SQLERRM;=C2=A0 =C2=A0insert into = cidrmgmt.errorlog(stamp, os_user, host, module, errorcode, erromsg) values = (CURRENT_TIMESTAMP, sys_context('userenv','session_user'), sys_context('use= renv','host'),v_Module, v_ErrorCode, v_ErrorMsg);END;RETURN NEW;end$BODY$LA= NGUAGE 'plpgsql';CREATE TRIGGER tr_stg_adjudicative_status_ins BEFORE INSER= T ON cidr_staging.stg_adjudicative_status FOR EACH ROW=C2=A0 EXECUTE PROCED= URE cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins();=C2=A0the tab= le definition is: create table cidr_staging.stg_adjudicative_status (receipt_number character= varying (13) not null,adjudicative_action_date character varying(8),=C2=A0= adjudicative_time_stamp character varying(4),adjudicative_status character = varying(50,mig_file_name character varying(80) not null,mig_insert_dt times= tamp without time zone,mig_modified_dt timestamp without time zone,mig_seg = bigint not null); Triggers:"tr_stg_adjudicative_status_ins" before INSERT ON cidr_staging.stg= _adjudicative_status FOR EACH ROW EXECUTIVE PROCEDURE cidr_staging.trigger_= fct_tr_stg_adjudicative_status_ins() even I take the "E" values out the error is still occurred but if I dropped= the function and trigger, the insert is fine thank you so much for looking into this error.=C2=A0Bach-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_1866228_495162720.1549913142120 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello,

I am still getting ERROR: invalid input syntax for integer:"42P01" when I am insert a data= into the table.
CONTEXT: PL/pgSQL function cidr_stag= ing.trigger_fct_tr_stg_adjudicative_status_ins() line 29 at assignment.
Insert query:
insert into cidr_sta= ging.stg_adjudicative_status (receipt_number, adjudicative_action_date, adj= udicative_time_stamp, adjudicative_status, mig_filename, mig_insert_dt, mig= _modified_dt, mig_seq) values (E'LIN1890030316', E'20180409',E'0752',E'Denn= ied',E'c3_20180626_adjudicativestatus_000.txt',NULL,NULL,172011);
the trigger is:
Create or replace functi= on cidr_staging.trigger_fct_tr_stg_adjudicative_status_in() RETURNS trigger= AS $BODY$
declare
v_seq bigint= :=3D0;
v_ErrorCode bigint;
V_Er= rorMsg varchar(512)'
v_Module varchar(32):=3D 'TR_STG= _ADJUDICATIVE_STATUS_INS';
BEGIN
BEGIN
select nextval('sq_staging') into STRICT v_se= q;
if NEW.mig_seg is null then
=    NEW.mig_seq:=3Dv_seq;
enf if;
if NEW.mig_filename is null then
  &= nbsp;NEW.mig_filename :=3D'Unknown';
end if;
exception
when others then
   v_ErrorCode :=3D SQLSTATE'
&nb= sp;  v_ErrorMsg :=3D SQLERRM;
   inser= t into cidrmgmt.errorlog(stamp, os_user, host, module, errorcode, erromsg) = values (CURRENT_TIMESTAMP, sys_context('userenv','session_user'), sys_conte= xt('userenv','host'),v_Module, v_ErrorCode, v_ErrorMsg);
END;
RETURN NEW;
end
<= div dir=3D"ltr" style=3D"margin-top: 0px; margin-bottom: 1em; border: 0px; = font-stretch: inherit; line-height: inherit;" id=3D"yui_3_16_0_ym19_1_15499= 09664966_46178">$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER tr_stg_adjudicative_status_ins BEFORE INSERT ON = cidr_staging.stg_adjudicative_status FOR EACH ROW
&nb= sp; EXECUTE PROCEDURE cidr_staging.trigger_fct_tr_stg_adjudicative_status_i= ns();
 
the table definition is:
create tabl= e cidr_staging.stg_adjudicative_status (
receipt_number character varying (13) not = null,
ad= judicative_action_date character varying(8), 
adjudicative_time_stamp characte= r varying(4),
adjudicative_status character varying(50,
mig_file_name character varying(80)= not null,
mig_insert_dt timestamp without time zone,
mig_modified_dt timestamp without tim= e zone,
= mig_seg bigint not null);

= Triggers:"tr_stg_adjudicative_status_ins" before INSERT ON cidr_staging.stg= _adjudicative_status FOR EACH ROW EXECUTIVE PROCEDURE cidr_staging.trigger_= fct_tr_stg_adjudicative_status_ins()


even I take the "E" values out the error is still occurred but if I= dropped the function and trigger, the insert is fine


thank you so much for looking i= nto this error.
 
Bach-Nga

No one in this world is pure and perfect.&= nbsp; If you avoid people for their mistakes you will be alone. So judge le= ss, love and forgive more.
To cal= l 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 g= enerously **Care deeply **Speak kindly.
*** Genuinely rich *= ** Faithful talent *** Sharing success
------=_Part_1866228_495162720.1549913142120--