Re: copy table from...

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(at)beansindustry(dot)co(dot)uk
> http://www.beansindustry.co.uk