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 1grOlW-0003sv-F8 for pgsql-admin@arkaria.postgresql.org; Wed, 06 Feb 2019 15:05:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1grOlV-0000vU-8R for pgsql-admin@arkaria.postgresql.org; Wed, 06 Feb 2019 15:05:29 +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 1grOlU-0000sP-Sb for pgsql-admin@lists.postgresql.org; Wed, 06 Feb 2019 15:05:29 +0000 Received: from sonic317-27.consmr.mail.bf2.yahoo.com ([74.6.129.82]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1grOlR-0001z4-0g for pgsql-admin@lists.postgresql.org; Wed, 06 Feb 2019 15:05:28 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1549465522; bh=mb84UJUxvijDulWXgEcR5+wqRtA9RARaB4Ao4OHU/Yo=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=hFVl7nIeypQForD/68qPr4qCp6GNxM4hR/XZYUyyenaj1pokw02naezSdhFAO663IsvYppkGH3JzYjD6NlB4cAqtRo+RRd3j612bVvU0dZ7a/VjNB9O3beKE03GGKw3fL8MtngNMaQoEI5WwQXdi8mTS0xb6CaXZTzcwpftG/oljiwLUSrAcuwetDJf2Nb1AVJZhgLml69exk1uuuIwfT3TVGDNMpS561iFWY8Cg/aqhWy9PhQmhow4xBxlhMKwr883bcwV/dm7Hh3s+zXhykaGYFMTRTHEYEQEyZzHv5UZKSpjO5bl4Nw9ZauyIZXViWbl5LxJ7Uy5/3bKFvwGxSg== X-YMail-OSG: ejR.RDoVM1n2HcCakIJwVICROJ_GzY3IRoyOjZQQwGEbSr2E19FHJf_wOANfZDd pgFSLdh0XFaUBm3nGpiSEi4ngh2Rf5j9DFtvdR78Co6UlVF0OJ.pw10ADO0MLgyJ4Fa33anGA06X PHxcmk0UnjHg9yI5luiMpuTLUNCAi1EyfC4OHF6vAn3SZUZlXOSt3HpAuIlCEVc4NgB0peWqFtSS dMwCHNxYP5ZqkEDYo72ZBrH8eQrMYFqtcAmK2jtMyrA9vMRuZRfr3s6RqSxr5v947LbDcueB_PLO wfdDx5VwiBDLdOZJRoQX_72T4AElen5doINcOmox1yOoNv0Upi90LvMFcNAQuqAriVT2PFAT9bPR pXdGFbsxEeoVfZnjHSiJKpkXB.Ay3Biy2Z8RuwcF63b4HboL6iKkVq7cGgAzDQV9JAsrHZCJeGO_ OHQgIUBQBOog.UtNvbGVv7RoJetBQZEOMLB18hw4zERLPipV787tD1kNaT8l3Q_.anWcRuB8zRWi tTnv.y3T6JlYWwJFgm8TSfThnkgjB7sLP3dMyPvvyRv6M1Cvxjs1bBeYcfGOkUjYapg0NIGk64PV KvoQiXf4d_bABpAcuMxt2yufT.8TdTJCRxye5y6Lu4Gq8T30a407k31MhBLTUYH3Rc1zvEshbyzp S9rOr7y6zxR59iOQg73BK4zlhdJ.nxYeNvyyWrBu0u8H_gUhMv57v1o7_H.hbGIe.qLCQCUBoI7h r3u.ODegcSs.eA.IcYMkCLSzJ2mBGOXPB3.EMxQqiKUTNGsOqDBahn0fA4USnr0E0B08B4HRpevv km7DvrL5yuHYRyCGITy7OUsCjcKBH8e2mYZZY1TXau.nGayqZx4RlUCngj8DJRpPHPWz6LOlGygu X4DyD8eqwroU9QeLzPm8TTrisVCAve3jjtzdTP6Zyi8DX7MdG29kpL6_YJSqOqOZjVvbTPrY2iH9 YzGBC8YX.zRCIPycgY2YWqrrq_WxtBabfIuQbNWdsyIkoyBfhbADhyfKs1oK0tVxJSBCjKc7OEe5 GbMGnkPfxXDRhGU2oJE4emfAz6OXud00VU33uxK3Q2UQPeIRHM8tJANJiD9w.FOtHBHhKz7WhHKC rlyvA Received: from sonic.gate.mail.ne1.yahoo.com by sonic317.consmr.mail.bf2.yahoo.com with HTTP; Wed, 6 Feb 2019 15:05:22 +0000 Date: Wed, 6 Feb 2019 15:05:12 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pepe TD Vo , Shreeyansh Dba Cc: Andrew Gierth , Scott Ribe , Pgsql-admin Message-ID: <192333709.3965062.1549465512951@mail.yahoo.com> In-Reply-To: <1110130949.1383907.1549030709802@mail.yahoo.com> References: <1861353148.992207.1548962087385.ref@mail.yahoo.com> <1861353148.992207.1548962087385@mail.yahoo.com> <49E280F0-7021-4FD7-AA99-985A86FA899C@elevated-dev.com> <2031259937.990023.1548963344432@mail.yahoo.com> <5D93F164-7317-43F6-B685-362779757250@elevated-dev.com> <729416431.1006652.1548963751866@mail.yahoo.com> <524354657.997228.1548963965240@mail.yahoo.com> <3842423F-D68D-4BD3-AB34-7508B01B2A9C@elevated-dev.com> <1225497522.1016680.1548965545454@mail.yahoo.com> <8004E05B-CAFA-4620-8E10-09A796064C9B@elevated-dev.com> <677786735.1029265.1548966193847@mail.yahoo.com> <890332297.1033268.1548966904362@mail.yahoo.com> <871s4sa63w.fsf@news-spur.riddles.org.uk> <232412720.1359751.1549027250299@mail.yahoo.com> <1110130949.1383907.1549030709802@mail.yahoo.com> Subject: Re: insert aborted commands ignored MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3965061_606013574.1549465512945" 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: 41563 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3965061_606013574.1549465512945 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable sorry, my server was out of network for couple days. I have tried to remove all " E' " of each column and when i run the insert = script.=C2=A0 I get another error, "value too long for type character varyi= ng(34).=C2=A0 How do I know which table sorry, my server was out of network for a coupole days.=C2=A0=C2=A0I have t= ried to remove all "=C2=A0E'=C2=A0" of each column and rerun the insert scr= ipt.=C2=A0 I get an error,=C2=A0"value too long for type character varying(= 34)."I am unclear as how and which table this error message upon attemting?= Also, when I run another schema insert script, I get right away as same as = the one above after remove=C2=A0ERROR: invalid input syntax for integer:"42= P01"CONTEXT: PL/pgSQL function cidr_staging.trigger_fct_tr_stg_adjudicative= _status_ins() line 29 at assignment. the trigger is:Create or replace function cidr_staging.trigger_fct_tr_stg_a= djudicative_status_in() RETURNS trigger AS $BODY$declarev_seq bigint:=3D0;v= _ErrorCode bigint;V_ErrorMsg varchar(512)'v_Module varchar(32):=3D 'TR_STG_= ADJUDICATIVE_STATUS_INS';BEGINBEGINselect nextval('sq_staging') 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_filename :=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.errorlo= g(stamp, os_user, host, module, errorcode, erromsg) values (CURRENT_TIMESTA= MP, sys_context('userenv','session_user'), sys_context('userenv','host'),v_= Module, v_ErrorCode, v_ErrorMsg);END;RETURN NEW;end$BODY$LANGUAGE 'plpgsql'= ;CREATE TRIGGER tr_stg_adjudicative_status_ins BEFORE INSERT ON cidr_stagin= g.stg_adjudicative_status FOR EACH ROW=C2=A0 EXECUTE PROCEDURE cidr_staging= .trigger_fct_tr_stg_adjudicative_status_ins(); all scripts, tables/insert/procedure/triggers are inherited from ora2pg.=C2= =A0 I have corrected the scripts to make them work for tables, triggers, fu= nctions.=C2=A0 All created well excepted the inserts are the issue and I co= uldn't find much information for those errors.=C2=A0 Thank you for your hel= ps. 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 Friday, February 1, 2019 9:18 AM, Pepe TD Vo wrot= e: =20 it worked fine using oracle export and import to oracle unclass and then i= mport to postgres using=C2=A0Amazon RDS, the problem is AWS is for unclass = and currently I am doing in the high side and not using AWS.How do I export= oracle to postgres to avoid this issue? thank you.=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 Friday, February 1, 2019 8:54 AM, Shreeyansh Dba wrote: =20 Hi Pepe, It look like foreign key reference by child table, so you are trying to in= sert values into chile table "cidr_ds_roles" that don't match with Parent t= able 'cidr_roles".=20 Thanks & Regards, Shreeyansh DBA Team www.shreeyansh.com On Fri, Feb 1, 2019 at 6:51 PM Pepe TD Vo wrote: thank you for your tip.I ran a insert.sql again with AN_ERROR_STOP=3D1 and = output to insert.txt, I get:ERROR: insert or update on table "cidr_ds_roles= " violates foreign key constraint "cidr_ds_roles_cidr_roles_fk1"DETAIL: key= (role_id)=3D(3) is not present in table "cidr_roles" from insert.txt I get=C2=A0INSERT 0 1 (repeat for the rest of insert) thank you.=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 Thursday, January 31, 2019 4:09 PM, Andrew Gierth wrote: =20 >>>>> "Pepe" =3D=3D Pepe TD Vo writes: Pepe> here how I run the script Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt psql -v ON_ERROR_STOP=3D1 -U postgres -d CIDR < insert.sql > insert_cidrmgm= t.txt That tells psql to stop on the first error, so you'll be able to see what the real error was. Your .txt file does not capture the error because you redirected only stdout, and errors go to stderr instead. --=20 Andrew (irc:RhodiumToad) =20 =20 =20 ------=_Part_3965061_606013574.1549465512945 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
sorry, my server was out of network for couple days.

I have tried to= remove all " E' " of each column and when i run the insert script.  I= get another error, "value too long for type character varying(34).  H= ow do I know which table



<= div style=3D"margin-top: 0px; margin-bottom: 1em; border: 0px; font-variant= -numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit;= line-height: inherit; font-family: Arial, "Helvetica Neue", Helv= etica, sans-serif; font-size: 15px; vertical-align: baseline; box-sizing: i= nherit; clear: both; color: rgb(36, 39, 41);" id=3D"yui_3_16_0_ym19_1_15494= 60320298_36211" dir=3D"ltr">sorry, my server was out of network for a coupo= le days.  
I have tried to remo= ve all " E' " of each column and rerun the insert script.  = I get an error, "value too long for type character varying(34)."
I am unclear as how and which table this erro= r message upon attemting?
Also, when I ru= n another schema insert script, I get right away as same as the one above a= fter remove 
ERROR: invalid input sy= ntax for integer:"42P01"
CONTEXT: PL/pgSQ= L function cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins() line 2= 9 at assignment.

the trigger is:
Create or = replace function cidr_staging.trigger_fct_tr_stg_adjudicative_status_in() R= ETURNS trigger AS $BODY$
declare
v_seq bigint:=3D0;
v_ErrorCode bigint;
V_ErrorMsg varcha= r(512)'
v_Module varchar(32):=3D 'TR_STG_= ADJUDICATIVE_STATUS_INS';
BEGIN
BEGIN
select nex= tval('sq_staging') into STRICT v_seq;
if = NEW.mig_seg is null then
   NEW= .mig_seq:=3Dv_seq;
enf if;
if NEW.mig_filename is null then
   NEW.mig_filename :=3D'Unknown';
end if;
exception
when others then
   v_ErrorCode :=3D SQLSTATE'
   v_ErrorMsg :=3D SQLERRM;
   insert into cidrmgmt.errorlog(stamp, os_user, host, module, = errorcode, erromsg) values (CURRENT_TIMESTAMP, sys_context('userenv','sessi= on_user'), sys_context('userenv','host'),v_Module, v_ErrorCode, v_ErrorMsg)= ;
END;
= RETURN NEW;
end
$BODY$
LANGUAGE 'plpgsql';
=
CREATE TRIGGER tr_stg_adjudicative_status_ins = BEFORE INSERT ON cidr_staging.stg_adjudicative_status FOR EACH ROW
  EXECUTE PROCEDURE cidr_staging.trigger_fct= _tr_stg_adjudicative_status_ins();

all scripts, tables/insert/procedure/trigg= ers are inherited from ora2pg.  I have corrected the scripts to make t= hem work for tables, triggers, functions.  All created well excepted t= he inserts are the issue and I couldn't find much information for those err= ors.  Thank you for your helps.
=
<= b id=3D"yui_3_13_0_rc_1_16_1381789421038_18">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 a= dmit 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 **Spea= k kindly.
*** Genuinely rich *** Faithful talent *** Sharing= success


On Friday, February 1, 2019 9:18 AM, Pepe TD Vo <pepevo@yahoo.co= m> wrote:


it worked fine using oracle= export and import to oracle unclass and then import to postgres using = ;Amazon RDS, the problem is AWS is for unclass and cu= rrently I am doing in the high side and not using AWS.
How do I export oracle to postgres to avoid this issue?
=
=
thank you.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for t= heir mistakes you will be alone. So judge less, love and forgive more.
T= o 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 Gingo= ld)

**Li= ve simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent= *** Sharing success


On Friday,= February 1, 2019 8:54 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> = wrote:


<= div>
Hi Pepe,

It look = like foreign key reference by child table, so you are trying to insert valu= es into chile table "cidr_ds_roles" that don't = match with Parent table 'cidr_roles".


Thanks & Regards,
Shreeyansh = DBA Team
www.shreeya= nsh.com


On Fri, Feb 1, 2019 at 6:51 PM Pepe TD Vo <pepevo@yahoo.com> wrote= :
thank you for your tip.
I ran a insert.sql again with AN_ERROR= _STOP=3D1 and output to insert.txt, I get:
ERROR: insert or update on table "cidr_ds_r= oles" violates foreign key constraint "cidr_ds_roles_cidr_roles_fk1"=
DETAIL: key (role_id)= =3D(3) is not present in table "cidr_roles"

INSERT 0 1 (repeat for the rest of insert)

thank you.
&n= bsp;
= Bach-Nga

No one in thi= s world is pure and perfect.  If you avoid people for their mistakes y= ou will be alone. So judge less, 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 generously **Care deeply **Speak kind= ly.
*= ** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11= .riddles.org.uk> wrote:


>>>>> "Pepe" =3D= =3D Pepe TD Vo <pepevo@ya= hoo.com> writes:

Pepe> here= how I run the script
Pepe> $ psql -U postgres -d CID= R < insert.sql > insert_cidrmgmt.txt

psql -v ON_ERROR_STOP=3D1 -U postgres -d CIDR < insert.sql > ins= ert_cidrmgmt.txt

That tells psql to st= op on the first error, so you'll be able to see
what the = real error was.

Your .txt file does no= t capture the error because you redirected only
stdout, a= nd errors go to stderr instead.

-- Andrew (irc:RhodiumToad)








------=_Part_3965061_606013574.1549465512945--