From: Chuck D. <pgsql-list@nullmx.com>
To: pgsql-sql@postgresql.org
Subject: Re: request for help with COPY syntax
Date: Thu, 25 Oct 2007 22:26:17 -0600
Message-ID: <200710252226.17852.pgsql-list@nullmx.com> (raw)
In-Reply-To: <2080.1193368930@sss.pgh.pa.us>
References: <043e01c8174c$61157a50$8f01010a@iptel.com.ar>
<200710252016.16121.pgsql-list@nullmx.com>
<2080.1193368930@sss.pgh.pa.us>
On October 25, 2007 09:22:10 pm you wrote:
> Did the sed actually do anything? (Hint: the file size of
> geonames_fixed.txt would be larger than geonames.txt if it did.
> Or you could diff the two files to confirm that something sensible
> happened.)
>
> I suspect that your shell may be fouling things up here. You may need
> to prepare this command as a one-line shell script, using an editor that
> doesn't barf on bare carriage returns ...
>
> regards, tom lane
OK, here's a bit more info after testing the sed on a bash line and in
a /bin/sh script edited with vi.
One of the troubling rows from the original file looks like this. This is a
couple fields, not the whole row.
cat -A short.txt
^IJishishan Bonanzu Dongxiangzu Salarzu Zizhixian^M$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian^M$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian^M$
^I2007-07-06$
Here's the shell script:
/bin/sed 's/^M/\\r/' < short.txt > short.out
And the result:
^IJishishan Bonanzu Dongxiangzu Salarzu Zizhixian\r$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian\r$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian\r$
^I2007-07-06$
Of course it sees the \r$ as the end of line and fails with:
ERROR: missing data for column "full_name"
Does that help diagnose?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql-list@nullmx.com
Subject: Re: request for help with COPY syntax
In-Reply-To: <200710252226.17852.pgsql-list@nullmx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox