Thread: \copy command

\copy command

From
dario billo
Date:
Hi all, I'm tryingto import data from a file with \copy command. The
file is like this:


000080110;98163 TOSTINE 15 FETTE;KG;4;1;1.48;1.91;910418

000080112;98164 TOSTINE 20 FETTE;KG;4;1;1.93;2.50;910420

000080128;98046 INVERNIZZINA MIGNON;N;4;1;0.83;1.06;910313

000080660;92642 GIRAVOLTE PLAIN    GR 200;N;10;1;1.98;2.78;910345

000080661;94460 GIRAVOLTE CORDON BL EU GR 200;N;10;1;1.98;2.78;910347

000080662;92318 GIRAVOLTE POMODORO    GR 200;N;10;1;1.98;2.78;910349

000080663;90489 ANNABELLA PZ 2X100 PF;KG;4;1;1.94;2.49;910656



the rows are all similar to these. I've used this command:

billo=> \copy  temp_listino from '/home/dario/listino.csv' using
delimiters ';'

where temp_listino description is:

billo=> \d temp_listino
                                  Table "temp_listino"
  Column   |     Type     |
Modifiers
-----------+--------------+--------------------------------------------------------------
 codart       | integer      |
 descr         | text         |
 um            | character(2) |
 iva            | smallint     |
 pezzatura  | integer      |
 cessione    | numeric(6,2) |
 pven         | numeric(6,2) |
 barcode    | integer      | not null default
nextval('"temp_listino_barcode_seq"'::text)
Unique keys: temp_listino_barcode_key

but the result is this:

\.
": can't parse "ne 1, pg_atoi: error in "
lost synchronization with server, resetting connection
billo=>

I don't understand why postgres block the import, and why "resetting
connection"? have you some ideas? Thanks all


Re: \copy command

From
Tom Lane
Date:
dario billo <dario.billo@inwind.it> writes:
> \.
> ": can't parse "ne 1, pg_atoi: error in "
> lost synchronization with server, resetting connection

Given the funny formatting of the error message, I'd bet you have a
newline problem.  COPY does not like anything but Unix-style newlines
(\n only); feeding it Windows-style newlines (\r\n) will lead to
failures like the above.

> why "resetting connection"?

Because there is no other way to recover from a COPY error in the
current FE/BE protocol.  (7.4 will improve that situation, but it
will require client-side rewrites to take advantage of the new
protocol...)

            regards, tom lane