Thread

1. copy table from...

   Sandeep Joshi <sjoshi@zambeel.com> — 2000-11-21T23:20:35Z

   I am trying to copy data from an ascii file into the table;
   I get following error. can anybody explain why? solution if possible;
    copy: line 1, pg_atoi: error in "tom": can't parse "tom "
   
   create sequence film_id start 1;
   create table zuser ( film_id int4 default NEXTVAL('film_id'), name
   char(10));
   
   data file has following contents:
   
   unbreakable
   sixthsense
   nerds
   buggy
   
   Sandeep
   
   
   

2. Re: copy table from...

   John Gray <jgray@beansindustry.co.uk> — 2000-11-22T08:48:31Z

   > I am trying to copy data from an ascii file into the table;
   > I get following error. can anybody explain why? solution if possible;
   >  copy: line 1, pg_atoi: error in "tom": can't parse "tom "
   > 
   
   Are you sure that "tom" isn't the first entry in the data file? If
   you're using the backend copy command remember that a relative path to a
   file may give you a file you don't expect!
    pg_atoi looks like an  ascii to integer conversion function .i.e. "tom
   " can't be parsed as an int. 
   
   Note that copy just copies the fields in in order -so a single column in
   your data file will be copied into film_id. (the sequence is only set as
   a default value if nothing else is provided for film_id). Obviously the
   contents of the single column won't parse.
   
   Even if you swap the column order, the documentation for copy says that
   it doesn't pay attention to rules and default values.
   
   The easiest solution is to use a temporary table:
   
   create table tmp_zuser (name char(10));
   copy tmp_zuser from ...
   
    create sequence film_id start 1;
    create table zuser ( film_id int4 default NEXTVAL('film_id'), name
   char(10));
   
   insert into zuser (name) select name from tmp_zuser;
   (the id value will be assigned at this stage)
   
   drop table tmp_zuser;
   
   Note that you could use "create temporary table" so that the table would
   get dropped at the end of the session automatically.
   
   Hopefully someone more knowledgable will correct me if I've got this all
   wrong!
   
   Regards
   
   John
   
   
   -- 
   John Gray
   Senior Technician
   BEANS INDUSTRY (UK) Ltd
   3 Brindley Place
   Birmingham B1 2JB
   Tel +44-121-698-8672
   Fax +44-121-698-8624
   mailto:jgray@beansindustry.co.uk
   http://www.beansindustry.co.uk
   
   
   

3. Re: copy table from...

   Sandeep Joshi <sjoshi@zambeel.com> — 2000-11-22T19:20:11Z

   Thanks. I think you are right. After thinking a bit I guessed the problem.
   
   Sandeep
   
   
   > > I am trying to copy data from an ascii file into the table;
   > > I get following error. can anybody explain why? solution if possible;
   > >  copy: line 1, pg_atoi: error in "tom": can't parse "tom "
   > >
   >
   > Are you sure that "tom" isn't the first entry in the data file? If
   > you're using the backend copy command remember that a relative path to a
   > file may give you a file you don't expect!
   >  pg_atoi looks like an  ascii to integer conversion function .i.e. "tom
   > " can't be parsed as an int.
   >
   > Note that copy just copies the fields in in order -so a single column in
   > your data file will be copied into film_id. (the sequence is only set as
   > a default value if nothing else is provided for film_id). Obviously the
   > contents of the single column won't parse.
   >
   > Even if you swap the column order, the documentation for copy says that
   > it doesn't pay attention to rules and default values.
   >
   > The easiest solution is to use a temporary table:
   >
   > create table tmp_zuser (name char(10));
   > copy tmp_zuser from ...
   >
   >  create sequence film_id start 1;
   >  create table zuser ( film_id int4 default NEXTVAL('film_id'), name
   > char(10));
   >
   > insert into zuser (name) select name from tmp_zuser;
   > (the id value will be assigned at this stage)
   >
   > drop table tmp_zuser;
   >
   > Note that you could use "create temporary table" so that the table would
   > get dropped at the end of the session automatically.
   >
   > Hopefully someone more knowledgable will correct me if I've got this all
   > wrong!
   >
   > Regards
   >
   > John
   >
   > --
   > John Gray
   > Senior Technician
   > BEANS INDUSTRY (UK) Ltd
   > 3 Brindley Place
   > Birmingham B1 2JB
   > Tel +44-121-698-8672
   > Fax +44-121-698-8624
   > mailto:jgray@beansindustry.co.uk
   > http://www.beansindustry.co.uk