Thread: copy data into table error

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

"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