copy data into table error

Started by Johnson, Shaunnover 23 years ago2 messagesgeneral
Jump to latest
#1Johnson, Shaunn
SJohnson6@bcbsm.com

Running PostgreSQL 7.2.1 on RedHat Linux 7.2.

I have a table that looks like this:

[snip]
drop table t_risk;
create table t_risk (
"ident" char(12),
"num" char(2)
);
[/snip]

I want to copy data from a flat text file. The file
is about 2000 rows and I've gone into vi to change
the delimiters ... it used to be a bunch of white spaced
between the columns, but I've changed that to a \t (tab).

The data looks like this:

[snip]

250860416 01
264137908 02
272385067 01

[/snip]

When I try to use the copy command, I get
an error:

[snip]

\copy t_risk FROM '/tmp/test.txt' USING DELIMITERS '\t'

ERROR: copy: line 1, value too long for type character(2)
lost synchronization with server, resetting connection
[/snip]

But when I change the table structure from char(2) to char(3)
the data gets imported. I'm thinking I have some odd dos
or control character (^M) in the column, but, when I use vi, I
can't find it and I've stripped out all spaces. What is
the error talking about? What is in the last column (text file)
that PostgreSQL is seeing that I (or, vi) can not?

Thanks for any pointers.

-X

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Johnson, Shaunn (#1)
Re: copy data into table error

"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:

But when I change the table structure from char(2) to char(3)
the data gets imported. I'm thinking I have some odd dos
or control character (^M) in the column, but, when I use vi, I
can't find it and I've stripped out all spaces. What is
the error talking about? What is in the last column (text file)
that PostgreSQL is seeing that I (or, vi) can not?

Are you sure the file is not being saved with Windows-style newlines
(^M^J)? COPY only likes Unix-style newlines (^J) --- it will see the
^M as a data character. (This behavior will likely change for 7.4
but that's how current releases behave.)

regards, tom lane