Обсуждение: insert aborted commands ignored
Hello expert DBAs,
I get the insert script from ora2PG and when I run the script, I get error
INSERT INTO cidrmgmt.search_results( id,scheduled_search_id,people_doc_id,receipt_number,form_number,a_number,first_name,middle_name,last_name,date_of_birth,dod,gender,country_of_birth,country_of_citizenship,country_of_residence,street,street2,city,state,zip,provice,postcal_code,country,res_street,res_city,res_state,res_zip,res_province,res_postal_code,res_country,ssn,tax_number,firm_name,service_center,last_indexed,ds_name,applicant_type,doe,citizenship,agency_code,tap_number,classification,mf_a_number, mf_first_name,mg_middle_name,mf_last_name,mf_agency_code, mf_tape_number,mf_fbi_result,mf_classification,indicator,inserted_date,updated_date,deleted_date) VALUES
(4896,92,E'7fdfcd2a-2da2-4f9f-bee3-4417a56c1a00',E'EAC1390072161','E'l130',NULL,E'JIEMEI',NULL,E'XIE',NULL,'1960-07-21 00:00:00',null,E'CHINA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,E'V','2017-1010 22:43:16',E'C3',E'Beneficiary',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,;2017-12-19 01:32:26','2018-04025 01:31:50','2018-01-11 01:33:44');
ERROR:current transaction is aborted, commands ignored until end of transaction block
I see each column has extra E' in front, is that right in postgres? How can I prevent this issue?
thank you for your input.
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.
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 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
> On Jan 31, 2019, at 12:14 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > ERROR:current transaction is aborted, commands ignored until end of transaction block > > I see each column has extra E' in front, is that right in postgres? How can I prevent this issue? The e should not be a problem, it's for "extended" strings, which allow some things that the SQL standard doesn't. But 'E'l130'is not legit; was that a copy/paste error. And you need to post the FIRST error in the logs, not the last one.
that is the first error. I get the same one over and over with
ERROR:current transaction is aborted, commands ignored until end of transaction block from the first one until the last one for each insert.
thank you,
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.
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 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
On Thursday, January 31, 2019 2:28 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jan 31, 2019, at 12:14 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> ERROR:current transaction is aborted, commands ignored until end of transaction block
>
> I see each column has extra E' in front, is that right in postgres? How can I prevent this issue?
The e should not be a problem, it's for "extended" strings, which allow some things that the SQL standard doesn't. But 'E'l130' is not legit; was that a copy/paste error.
And you need to post the FIRST error in the logs, not the last one.
> On Jan 31, 2019, at 12:35 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > that is the first error. I get the same one over and over with > ERROR:current transaction is aborted, commands ignored until end of transaction block from the first one until the lastone for each insert. Then you had a prior error, and are now continually trying to run commands in a transaction that is aborted, because of thatprior error, so rollback.
how to roll back?
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.
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 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
On Thursday, January 31, 2019 2:39 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jan 31, 2019, at 12:35 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> that is the first error. I get the same one over and over with
> ERROR:current transaction is aborted, commands ignored until end of transaction block from the first one until the last one for each insert.
Then you had a prior error, and are now continually trying to run commands in a transaction that is aborted, because of that prior error, so rollback.
>
> that is the first error. I get the same one over and over with
> ERROR:current transaction is aborted, commands ignored until end of transaction block from the first one until the last one for each insert.
Then you had a prior error, and are now continually trying to run commands in a transaction that is aborted, because of that prior error, so rollback.
> On Jan 31, 2019, at 12:42 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > how to roll back? rollback; You really need to set some time aside to read the docs.
I run by script and from the document show roll back with transaction_id. I don't see the transaction_id.
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.
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 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
On Thursday, January 31, 2019 2:44 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jan 31, 2019, at 12:42 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> how to roll back?
rollback;
You really need to set some time aside to read the docs.
>
> how to roll back?
rollback;
You really need to set some time aside to read the docs.
> On Jan 31, 2019, at 12:46 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > ...from the document show roll back with transaction_id. I don't see the transaction_id. I have no idea what you're talking about here. Rollback is a command that works within the current transaction. And of course, you need to know when the first error occurred.
sir,
there's no transaction in progress when i type rollback command. Also, all tables are emptiness right now.
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.
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 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
On Thursday, January 31, 2019 2:51 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jan 31, 2019, at 12:46 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> ...from the document show roll back with transaction_id. I don't see the transaction_id.
I have no idea what you're talking about here. Rollback is a command that works within the current transaction.
And of course, you need to know when the first error occurred.
>
> ...from the document show roll back with transaction_id. I don't see the transaction_id.
I have no idea what you're talking about here. Rollback is a command that works within the current transaction.
And of course, you need to know when the first error occurred.
> On Jan 31, 2019, at 1:12 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > there's no transaction in progress when i type rollback command. Also, all tables are emptiness right now. then there is an earlier error when you run the script
there's no error before I run insert.sql
I exported schema from oracle using ora2pg, I imported (by runing) tables.sql, procedure.sql, sequence.sql fine. When I execute insert.sql I get the error as I posted. Check the tables, they are empty, no data insert. Rollback, - no transaction in process.
thank you,
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.
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 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
On Thursday, January 31, 2019 3:16 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jan 31, 2019, at 1:12 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> there's no transaction in progress when i type rollback command. Also, all tables are emptiness right now.
then there is an earlier error when you run the script
>
> there's no transaction in progress when i type rollback command. Also, all tables are emptiness right now.
then there is an earlier error when you run the script
> On Jan 31, 2019, at 1:23 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > When I execute insert.sql I get the error as I posted. There MUST be an earlier error--that is what the error you have posted means. Check the output from insert.sql carefully.
here how I run the script
$ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
I got all repeat error .....
"ERROR: current transaction is aborted, commands ignored until end of transaction block "
even view insert_cidrmgmt.txt. All the same. There's not no other output.
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.
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 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
On Thursday, January 31, 2019 3:26 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jan 31, 2019, at 1:23 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> When I execute insert.sql I get the error as I posted.
There MUST be an earlier error--that is what the error you have posted means. Check the output from insert.sql carefully.
>
> When I execute insert.sql I get the error as I posted.
There MUST be an earlier error--that is what the error you have posted means. Check the output from insert.sql carefully.
> On Jan 31, 2019, at 1:35 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt > > I got all repeat error ..... > "ERROR: current transaction is aborted, commands ignored until end of transaction block " Is it possible you're not seeing output because your scrollback buffer is limited? Can you see the command as you enteredit, followed by the first line of output? Because there IS an earlier error, and you need to find it.
>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes: Pepe> here how I run the script Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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. -- Andrew (irc:RhodiumToad)
Thank you so much, I will run your command tomorrow.
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.
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 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
On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:
Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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.
--
Andrew (irc:RhodiumToad)
Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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.
--
Andrew (irc:RhodiumToad)
thank you for your tip.
I ran a insert.sql again with AN_ERROR_STOP=1 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)=(3) is not present in table "cidr_roles"
from insert.txt I get
INSERT 0 1 (repeat for the rest of insert)
thank you.
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.
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 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
On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:
Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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.
--
Andrew (irc:RhodiumToad)
Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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.
--
Andrew (irc:RhodiumToad)
Hi Pepe,
It look like foreign key reference by child table, so you are trying to insert values into chile table "cidr_ds_roles" that don't match with Parent table 'cidr_roles".
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=1 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)=(3) is not present in table "cidr_roles"from insert.txt I getINSERT 0 1 (repeat for the rest of insert)thank you.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 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 successOn Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:
Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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.
--
Andrew (irc:RhodiumToad)
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 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.
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.
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 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
On Friday, February 1, 2019 8:54 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Pepe,
It look like foreign key reference by child table, so you are trying to insert values into chile table "cidr_ds_roles" that don't match with Parent table 'cidr_roles".
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=1 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)=(3) is not present in table "cidr_roles"from insert.txt I getINSERT 0 1 (repeat for the rest of insert)thank you.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 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 successOn Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:
Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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.
--
Andrew (irc:RhodiumToad)
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). How do I know which table
sorry, my server was out of network for a coupole days.
I have tried to remove 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 error message upon attemting?
Also, when I run another schema insert script, I get right away as same as the one above after remove
ERROR: invalid input syntax for integer:"42P01"
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_adjudicative_status_in() RETURNS trigger AS $BODY$
declare
v_seq bigint:=0;
v_ErrorCode bigint;
V_ErrorMsg varchar(512)'
v_Module varchar(32):= 'TR_STG_ADJUDICATIVE_STATUS_INS';
BEGIN
BEGIN
select nextval('sq_staging') into STRICT v_seq;
if NEW.mig_seg is null then
NEW.mig_seq:=v_seq;
enf if;
if NEW.mig_filename is null then
NEW.mig_filename :='Unknown';
end if;
exception
when others then
v_ErrorCode := SQLSTATE'
v_ErrorMsg := SQLERRM;
insert into cidrmgmt.errorlog(stamp, os_user, host, module, errorcode, erromsg) values (CURRENT_TIMESTAMP, 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_staging.stg_adjudicative_status FOR EACH ROW
EXECUTE PROCEDURE cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins();
all scripts, tables/insert/procedure/triggers are inherited from ora2pg. I have corrected the scripts to make them work for tables, triggers, functions. All created well excepted the inserts are the issue and I couldn't find much information for those errors. Thank you for your helps.
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.
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 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
On Friday, February 1, 2019 9:18 AM, Pepe TD Vo <pepevo@yahoo.com> 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 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.
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.
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 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
On Friday, February 1, 2019 8:54 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Pepe,
It look like foreign key reference by child table, so you are trying to insert values into chile table "cidr_ds_roles" that don't match with Parent table 'cidr_roles".
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=1 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)=(3) is not present in table "cidr_roles"from insert.txt I getINSERT 0 1 (repeat for the rest of insert)thank you.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 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 successOn Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:
Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.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.
--
Andrew (irc:RhodiumToad)
> On Feb 6, 2019, at 8:05 AM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > I have tried to remove all " E' " of each column Why???
On Wed, Feb 6, 2019 at 8:05 AM Pepe TD Vo <pepevo@yahoo.com> wrote: > get another error, "value too long for type character varying(34). 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. Its better, IMO, to avoid using "varchar(n)" in your schema anyway. 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.
Thank you sir,
I have tried one by one to show create table back and found a few fields with character varying(34) and I don't know exactly which one failed. 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 implicit column list left to right and I need to check one by one column and see what they are. 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. 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 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. Even without setting "-v ON_ERROR_STOP=1 ". 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 again.
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.
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 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
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 Pepe TD Vo <pepevo@yahoo.com> wrote:
> get another error, "value too long for type character varying(34). 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. Its better, IMO, to avoid using "varchar(n)" in your
schema anyway. 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.
> get another error, "value too long for type character varying(34). 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. Its better, IMO, to avoid using "varchar(n)" in your
schema anyway. 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.
Mr. Johnston,
for the error, "value too long for type character varying(34)thank you for your information. I resolved the problem.
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 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
On Wednesday, February 6, 2019 2:59 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
Thank you sir,
I have tried one by one to show create table back and found a few fields with character varying(34) and I don't know exactly which one failed. 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 implicit column list left to right and I need to check one by one column and see what they are. 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. 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 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. Even without setting "-v ON_ERROR_STOP=1 ". 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 again.
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.
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 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
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 Pepe TD Vo <pepevo@yahoo.com> wrote:
> get another error, "value too long for type character varying(34). 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. Its better, IMO, to avoid using "varchar(n)" in your
schema anyway. 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.
> get another error, "value too long for type character varying(34). 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. Its better, IMO, to avoid using "varchar(n)" in your
schema anyway. 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.