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 1gtdBp-0007lS-66 for pgsql-admin@arkaria.postgresql.org; Tue, 12 Feb 2019 18:53:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gtdBl-0005bE-PO for pgsql-admin@arkaria.postgresql.org; Tue, 12 Feb 2019 18:53:49 +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 1gtdBl-0005al-4y for pgsql-admin@lists.postgresql.org; Tue, 12 Feb 2019 18:53:49 +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_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtdBh-0000I4-KM for pgsql-admin@lists.postgresql.org; Tue, 12 Feb 2019 18:53:47 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1549997623; bh=SJKPgTARjvC8tdroUxgpoWis+A8fb6gKyirwd1xSkxc=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=kEUxvKqWYcP1Iu/wFgXZOPfDvT9HGhLlYJhaDHdyLvehm5j/TOijK5BxpRB7L4HjEET41Fpsol8MrHBJOdP5AZsBFfZns5B0dtQ4TvBYu+l89TGBNKjrB61zQc1STN0R46PUCbFBUKBwjSkKBbxsKlZwkQ3941iXq7Qq0L3ef7bue6rH0Z7RHk0BhuVpWpgN86c0UxeRpKxYBbrStP003QyHmW+kBrGf5uNLmCq/A6LDUpxMjpKUeLqMtBE/GblfbzwYbbDRZY4qqEn5DW0XuxrrBLtu/KqR/RyBfay5hmBQ4q3r0dt6W7JLoHytprkH+W97nBFFh7P3gAoG9GVzvQ== X-YMail-OSG: 2QVIR48VM1lM5Rx2no6Q4aTdGdsHz0RcHIiX.bCX6VONfE.9m5cNub.5MBwAAIX Itbtfm3LSHClXg32SfeUWJP8HUFECEBLVC_HpL93h6rBeQ_eQXwRQ5Qh3HfH9qwGZacs.Y1AN_Pi F.z4HfLJYhg1YNRqrm8qvfiCLjFAMM67h2VFwITgz.mPsAFwqY0sHcAZVMJETJKnchD37dqELG9l 3_QMwGyPYde_qWXnDgCY6L4e0XNs_8ypQhJ2qG00uSWSoyg68K0eFobI0wJis7YjxGJRO8TyPEDd DlHIUrVuAne_.aQujV1gWdVIvSGb1oTG.gPYVLWg4g3_kQJohKkKqOkIB6sbMpdZsWjgWwbZ3.44 6cfaR.dy56wuxMLpTiRpIAnF6xvwWG8AdaV3EPmtJnYSx21Oq3GPodplQi7THBOdlQHiMEVijEdf zBsHAPQtMulEqnhwwfMOnZTbMQn0unXwSNvW7k5lOMGHy4vfq.YrHy9JJue598qt7I0UZKmMsnEa mOP_PW00OGvJGGtaf2l5CthqVGA1Vys.zlvzZVP7RivE8XsPM_ub0hBZHrXRDyj3BWQdTKqFYhto pq9esjuIm6xjyZbbZg5hamccMcRFcsGcvkcxz8XO.eO4uhQrjC9_WUBE2m4piB3iAW_d30hl774m SC0EtSKDz.sVQ5XJm.4AEDsHRlamJi6Q8CRWaT.lkNCtfob9bazivkdhmfGSZQMYPxgr37qQXegC Ync6VFcOiMXOAXYgBsMgJg4S4Aqx67VYHFCgyDoFIWvn_fClHNmCSbe25QeyfqS3AFsMoUFiCGNV FuJiYH0XoaHXpL1qND6nfwY_OQ0UlxwuyGhf6VVJTYtzDm6eUayP5uL0LgxVmkpU4niuMpZWWTIj Oa5BFr_UhA1GTVzLwqRm4KzZOHrGl5qyIkb5jsHQeZzbsW.6nqSZgCywxJxqjmbarsgePmuUSkPW Zn8qJbQXXeaStTVe4hb37.YPUplD95Cf8bjywfAesdX9HMwhw9WbpnlBx0eyirsPqvc2nVcR26P. szzUWbDGuVsgxspcKwUTR8xx0P40PqUi9jGg6viJgwkTfS.YMomdwc6KDSoIKfXTDb0I- Received: from sonic.gate.mail.ne1.yahoo.com by sonic313.consmr.mail.bf2.yahoo.com with HTTP; Tue, 12 Feb 2019 18:53:43 +0000 Date: Tue, 12 Feb 2019 18:53:42 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: "David G. Johnston" Cc: Pgsql-admin Message-ID: <1722416922.2505642.1549997622888@mail.yahoo.com> In-Reply-To: References: <1990155251.2422933.1549988748913.ref@mail.yahoo.com> <1990155251.2422933.1549988748913@mail.yahoo.com> Subject: Re: insert data with invalid input syntax for integer:"42P01" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2505641_186791529.1549997622886" 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: 7922 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2505641_186791529.1549997622886 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I did and first I changed the data type around as I mention yesterday.=C2= =A0 Today I removed the exception to test out.=C2=A0 Nothing wrong with the= exception but I find out the problem is "relation "sq_staging" does not ex= it, but the sequence does exist from information_schema.sequences/ Idropped= and recreated sequence successful but when I do the simple select nextval(= 'sq_staging');=C2=A0ERROR: relation "sq_staging" does not exist.=C2=A0 I dr= opped all sequences and make sure they existed before rebuild the function = and trigger.=C2=A0=C2=A0The insert is fine now. Thank you you all.v/r,=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=20 On Tuesday, February 12, 2019 11:31 AM, David G. Johnston wrote: =20 On Tue, Feb 12, 2019 at 9:25 AM Pepe TD Vo wrote: > > I just test out to remove " E' " to see it work because in the near futur= e the syntax convert the data insert into Postgres will be without " E' " > > I gave exact code converted from Oracle via Ora2pg without changing any.= =C2=A0 I just worked around to change the data type to see it work unfortun= ately, it didn't work :( .=C2=A0 The insert is working with function but as= soon as I put the trigger in, I get an error. Its possible my diagnosis was incorrect (or incomplete, is it the exact same error?) but I don't intend to go back and look at the original email and dive deeper.=C2=A0 You have too many moving parts at the moment and you need to simplify the code involved by removing components until you don't get an error and then add them back one at a time until you get an error - then figure out why the last added piece of code is wrong. David J. =20 ------=_Part_2505641_186791529.1549997622886 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I did and first I changed the data type around as I = mention yesterday.  Today I removed the exception to test out.  N= othing wrong with the exception but I find out the problem is "relation "sq= _staging" does not exit, but the sequence does exist from information_schem= a.sequences/ I
dropped = and recreated sequence successful but when I do the simple select nextval('= sq_staging'); 
ERR= OR: relation "sq_staging" does not exist.  I dropped all sequences and= make sure they existed before rebuild the function and trigger.  = ;The insert is fine now.

Thank you you all.
v/r,
&nbs= p;
Bach-Nga

No one in this world is pure and perfect.  If you a= void people for their mistakes you will be alone. So judge less, love and f= orgive 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 wel= l, he was a perfect gentleman (Hermione Gingold)
=
**Live simply **Love generously **Care deeply *= *Speak kindly.
*** Genuinely rich *** Faithful talent *** Sh= aring success

On Tuesday, February 12, 2019 11:31 AM, David G. Johnston &l= t;david.g.johnston@gmail.com> wrote:


On Tue, Feb 12, 2019 at 9:25 AM Pep= e TD Vo <pepevo@yahoo.com> wrote:
&= gt;
> I just test out to remove " E' " to see it work = because in the near future the syntax convert the data insert into Postgres= will be without " E' "
>
> I gav= e exact code converted from Oracle via Ora2pg without changing any.  I= just worked around to change the data type to see it work unfortunately, i= t didn't work :( .  The insert is working with function but as soon as= I put the trigger in, I get an error.

Its possible my diagnosis was incorrect (or incomplete, is it the
exact same error?) but I don't intend to go back and look at the<= br clear=3D"none">original email and dive deeper.  You have too many m= oving parts at the
moment and you need to simplify the co= de involved by removing
components until you don't get an= error and then add them back one at
a time until you get= an error - then figure out why the last added
piece of c= ode is wrong.


David J.


------=_Part_2505641_186791529.1549997622886--