Thread: copy table from...

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

> 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

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