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 1grTLz-0001UP-8g for pgsql-admin@arkaria.postgresql.org; Wed, 06 Feb 2019 19:59:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1grTLw-0006Ve-DX for pgsql-admin@arkaria.postgresql.org; Wed, 06 Feb 2019 19:59:24 +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 1grTLv-0006VW-Kp for pgsql-admin@lists.postgresql.org; Wed, 06 Feb 2019 19:59:24 +0000 Received: from sonic314-14.consmr.mail.bf2.yahoo.com ([74.6.132.124]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1grTLn-0003XZ-53 for pgsql-admin@lists.postgresql.org; Wed, 06 Feb 2019 19:59:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1549483152; bh=edgTCa2f8xCx/R3c9R+Uu64ABtbRwYCmalJN8icadfY=; h=Date:From:Reply-To:To:Cc:Subject:References:From:Subject; b=XfJOdJSlh1fACfzcU8scxgudPFGWzkLcdGaKpclOTIN3kRWgX4ogQ8AKbRurkHSYs9gqgfNRHaPC34xAYdQDmNuHzCELT37u1XYoVgTFIG1N3Kt42r3lHe//Gi1gxzUZmuHm4rTjKeNbwGeTNLdQKCR6+76W2nVjcKg0Q/f3c+uuvsuNLt4FUyzhCX1KuwPurXxqSL9Vp4reSVkLvh17RZdzK5UbgUkm/iZ73AugH5TjqCTyv6H5mjDP7hQ0rFW6/kG4oSE0WPUB5mh0KQzXPIyO4ZuUmSiOgSeHiLhczFJzPUp9KTiaxGoQ+l+DAliMRulqbGeKErqSiu5EJSGSBg== X-YMail-OSG: .7ZfIPUVM1nj1Gsxr9ofZw1wjPW7XGuuipQeyez4iSew_aMqDxIFmgeukLg6wfT EaVLta4kjnogaELezdgeq_f0AebDgPv2VofBLafAREbBy63KYmnLck7RLjhKWKBgS7y8DCRbUMxr _r.R.EL7IlblC84oD9fLXz_g2TMt5nrW2uTz1i3IdOYYl7dXCM6fNIqgIcU0L.9OsOgcXp.1FN4c MAOUzOMNcYjnflpl_273BPnI63VDBV2EMhhFGFbow2zb.1sRR5N9lonCqAu0lOCdQnR2CtX4kB.c MM.ZIOfsnpZLmepN0BoCyx3lrE2ABALNHV.04x6zWnbToXA3LcXe430sW9b3XsNmplPbyKKwxPCu AmoZs2USZfsrRQLB5yFaqBGw0eWZQxQklzatCbPvftTlvjbE48K2cQSjA3oyNkmhXkL6PRc_Zgx5 CANvLALkRwtVKLTG5UjOnP9WK2mOpMa65lctx7ndbXbrf3gmLaJlrz8wqcZlev3JBUKjnFY4wheF QTW2tnRZrabg6xtGxPGoyRqBmCMk3Qzpiy3vBWH.QnYWx4dHuV2omLVdQ5gvgFzOK8qt_zAqL.HW u2vJwOkblaq1vsebMO9_UrGt7HhZiqNsNTAqxksofEP0DHVid9aWZ.LS783zL3vJxw3VpHpA7Prr QoXubwIZmbmU_qimfPWhCB4vG1d.ZFiF4wln_vx9Jj9XBbbPgmsGMnTq8Lw0O4xgoYrRei6R7iOi KBPd2Q_CjUYxxL8ah09FhaMbqxb5aCcn8Fl2XtZO1BNcUtqdlZCatxlXshIQ8m2ReAtIbvc.L2D6 YSmC.1HiyKMl26LDwxd8mUoobc4iRjL7kBbmUAYG8.jh6oc2r.FVRkqFrs_P0_RATBlxJUbOCSEK m4gJdArrxkYMZDK_MAN_TNHgOxD_zx.AMjgcsPDnAb7qfsEEqQ1ml7i6X6lhbtbQItGTCc3KLz6h 4R5etjyQGHie4yd.Sp31KybwjXqOLvFrxqob2MkLcn9maBZDuOirJPqF0oe2GRgMKkAQ8ifPRmrF GRoDR_WKAAmDSoKHfPY430AFkf4TnnrT_TBZFSc5Qj2z_6DUieUrCKAqsab.xjiVCF6o9 Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.bf2.yahoo.com with HTTP; Wed, 6 Feb 2019 19:59:12 +0000 Date: Wed, 6 Feb 2019 19:59:07 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: "David G. Johnston" Cc: Shreeyansh Dba , Andrew Gierth , Pgsql-admin Message-ID: <722258958.4166300.1549483147294@mail.yahoo.com> Subject: Re: insert aborted commands ignored MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4166299_1047126920.1549483147292" References: <722258958.4166300.1549483147294.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: 11766 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_4166299_1047126920.1549483147292 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thank you sir, I have tried one by one to show create table back and found a few fields wi= th=C2=A0character varying(34) and I don't know exactly which one failed.=C2= =A0 =C2=A0The data type was right from Oracle and count the characters on t= he insert script was fine.=C2=A0 The target column names listed in order bu= t the values supplied by the values are associated with the explicit or imp= licit column list left to right=C2=A0 and I need to check one by one column= and see what they are.=C2=A0 The problems are so many and the more I query= , psql hung and I needed to ctrl C to get out from psql and get back in. I am checking one by one table insert row now.=C2=A0 I need to pull all ins= ert of each table out first and see if insert ok and so far it's ok.=C2=A0 = With your information I will check back and increase the values (n) and see= the insert work or not, since the insert script was 20Gb and it will stop = running as soon as the error kick in.=C2=A0 Even without setting "-v ON_ERR= OR_STOP=3D1=C2=A0".=C2=A0 The 1st 200 insert lines are ok from the psql pro= mpt but stop insert if I running psql -U postgres -d CIDR < insert_cidrdba.= sql > insert_cidrdba.txt.=C2=A0 I am inserting one by one from psql now.=C2= =A0 Hope to find where the error occurs. thank you for your input again. v/r, 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=20 On Wednesday, February 6, 2019 10:18 AM, David G. Johnston wrote: =20 On Wed, Feb 6, 2019 at 8:05 AM Pepe TD Vo wrote: >=C2=A0 get another error, "value too long for type character varying(34).= =C2=A0 How do I know which table How many tables in your schema are defined with a column having a type of "character varying(34)"? To narrow down if >1 you'd need to trace the load script and see at what point it is failing; or change half of them to "text", try again, change some back to varchar(34), try again repeat until only one of them is "text" and that is your culprit. Or just get rid of "varchar(n)" columns, make them all text and, for those were you really want to keep length limits, add explicit constraints. There have been discussions somewhat recently to make the error message itself more helpful but I don't believe anyone is actively working on it.=C2=A0 Its better, IMO, to avoid using "varchar(n)" in your schema anyway.=C2=A0 More precise check constraints are a better option not the least of which is because they can be named and self-identify with the table to which they are attached. David J. =20 ------=_Part_4166299_1047126920.1549483147292 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
=
Thank you sir,

I have tried one by one to show create table back= and found a few fields with character vary= ing(34) and I don't know exactly which one failed.&= nbsp;  The data type was right from Oracle and count the characters on= the insert script was fine.  The target column names listed in order = but the values supplied by the values are associated with the explicit or i= mplicit column list left to right  and I need to check one by one colu= mn and see what they are.  The problems are so many and the more I que= ry, psql hung and I needed to ctrl C to get out from psql and get back in.<= /div>

I am checking one by one table insert row now= .  I need to pull all insert of each table out first and see if insert= ok and so far it's ok.  With your information I will check back and i= ncrease the values (n) and see the insert work or not, since the insert scr= ipt was 20Gb and it will stop running as soon as the error kick in.  E= ven without setting "-v ON_ERROR_STOP=3D1 ".  The 1st 200 insert lines are ok= from the psql prompt but stop insert if I running psql -U postgres -d CIDR= < insert_cidrdba.sql > insert_cidrdba.txt.  I am inserting one = by one from psql now.  Hope to find where the error occurs.

thank you for your input aga= in.

v/= r,

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 th= ose who knew him well, he was a perfect gentleman (Hermione Gingold)=

**Live simply = **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Shari= ng success


=
On Wednesday, February 6, 2019 10:18 AM, = David G. Johnston <david.g.johnston@gmail.com> wrote:


On Wed, Feb 6, 2019 at 8:05 AM P= epe TD Vo <pepevo@yahoo.c= om> wrote:

>  get another error, "value too = long for type character varying(34).  How do I know which table
<= br clear=3D"none">
How many tables in your schema are def= ined with a column having a type
of "character varying(34= )"?

To narrow down if >1 you'd need= to trace the load script and see at
what point it is fai= ling; or change half of them to "text", try again,
change= some back to varchar(34), try again repeat until only one of
them is "text" and that is your culprit.

Or just get rid of "varchar(n)" columns, make them all text and, = for
those were you really want to keep length limits, add= explicit
constraints.

There have been discussions somewhat recently to make the error
message itself more helpful but I don't believe anyone is activel= y
working on it.  Its better, IMO, to avoid using "v= archar(n)" in your
schema anyway.  More precise chec= k constraints are a better option not
the least of which = is because they can be named and self-identify with
the t= able to which they are attached.

David= J.



------=_Part_4166299_1047126920.1549483147292--