Обсуждение: help with COPY query

Hi,
I want to load a file containning a column of FLOATS, into a table of
INTEGERs.

I tried:
    COPY "Field" ("myintegers") from './myfloats.txt';
but got an error message:
    ERROR:  invalid input syntax for integer: "15.0"
    CONTEXT:  COPY Field, line 1, column WatchFreq: "15.0"
How can I go around this problem? is there any way to force copy to do the
right casting?

Thanks in advance,
Elinor

Hi,

I think the better is to modify you file before.
With sed it should be something like :

sed "s/\(\tab[0-9][0-9]*\).[0-9][0-9]*\(\tab\)/\1\2/" file > newfile

where \tab is the tabulation (or ctrl-V ctrl-I)

You can do the same with vi

vi file
:%s/\(\tab[0-9][0-9]*\).[0-9][0-9]*\(\tab\)/\1\2/
:wq

Bruno


On Tue, 24 Feb 2004, Elinor Medezinski wrote:

> Hi,
> I want to load a file containning a column of FLOATS, into a table of
> INTEGERs.
>
> I tried:
>     COPY "Field" ("myintegers") from './myfloats.txt';
> but got an error message:
>     ERROR:  invalid input syntax for integer: "15.0"
>     CONTEXT:  COPY Field, line 1, column WatchFreq: "15.0"
> How can I go around this problem? is there any way to force copy to do the
> right casting?
>
> Thanks in advance,
> Elinor
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>

Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com

Elinor Medezinski <elinor@bellatrix.tau.ac.il> writes:
> I want to load a file containning a column of FLOATS, into a table of
> INTEGERs.
> ...
> How can I go around this problem? is there any way to force copy to do the
> right casting?

COPY is not designed to do data manipulation for you.  The best bet is
to load the data file into a temp table that has a column set matching
the data file, and then use INSERT ... SELECT to transform the data
and insert it into the final table.

            regards, tom lane