Re: pg_dump usage - problems with restore due to the use of tab delimiters

Hi,
    Thanks for the response, but I'm not sure I understand your comments.
I'm running Pgsql on RedHat Linux, and the varchar field is created from web
form input, which as we know, is dominated by Microsoft and IE. If I
translate CR/LF->LF before inserting into the database, then I have to
retranslate LF->CR/LF when reading it out of the database, to display in IE,
(LF appears as little black block characters in IE). So, I wish windows was
dead too, but, hey, there you have it. Anyway to handle CR/LF nicely in
pg_dump?
Thanks,
Rich

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rich Ryan" <rich@usedcars.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, November 08, 2001 5:59 AM
Subject: Re: [GENERAL] pg_dump usage - problems with restore due to the use
of tab delimiters


"Rich Ryan" <rich@usedcars.com> writes:
> Lots of my records have linebreaks in them (the ^M character), but only a
> few of them with a certain pattern of linebreaks seem to confuse
> pgsql.

*All* of them are wrong.  The ones that aren't causing an error are the
ones where the last field is a textual field --- and what's happening
there is that the ^M is being taken as a data character, which you
won't want.

Bottom line: do not allow Windoze to get its hands on a pg_dump script.
LF->CR/LF translation is not something you want to happen to that file.

regards, tom lane

--------- Original Message -------------

Hi all,
    I'm using pg_dump and pgsql to migrate from pgsql 7.0 to 7.1.3. The dump
goes fine, but upon recreating the database via pgsql, I get error messages
like:
ERROR:  copy: line 1587, Bad int8 external representation " "
PQendcopy: resetting connection

After doing a lot of work separating COPY commands into separate files and
running them individually, I was able to find the row causing the problem.
It looked like
123123123123    This is my var char field with lots of nasty end of line
characters from a web page textbox field.^M
There's one line break, ^M
There's another..^M
And a bunch at the end by themselves, that seem to cause the problem...^M
^M
^M
^M
\N

Lots of my records have linebreaks in them (the ^M character), but only a
few of them with a certain pattern of linebreaks seem to confuse pgsql. My
question is there any workaround to this, for example, any way I can get
pg_dump to use another field delimiter than tab?
Thanks much,
Rich Ryan