From: "Chuck D." <pgsql-list@nullmx.com>
Reply-To: pgsql-list@nullmx.com
To: pgsql-sql@postgresql.org
Subject: Re: request for help with COPY syntax
Date: Fri, 26 Oct 2007 10:29:56 -0600
User-Agent: KMail/1.9.6
References: <043e01c8174c$61157a50$8f01010a@iptel.com.ar>
	<200710252016.16121.pgsql-list@nullmx.com>
	<2080.1193368930@sss.pgh.pa.us>
In-Reply-To: <2080.1193368930@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200710261029.56284.pgsql-list@nullmx.com>

On October 25, 2007 09:22:10 pm Tom Lane 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 ...

Can I ask this, what is the objective here?

I have a field with ^M$ in it (shows as CR/LF in text editor).  The objective 
should be to replace the CR/LF with just a CR shouldn't it?

Or is the objective to replace the CR/LF with a literal \r?  I'm a bit 
confused by the output of COPY that says to use "\r".  Looking at other data 
in my database (user message) I'd have to say it needs to be replaced with a 
literal \r correct?

If that is the case, the sed script doesn't work because it is only replacing 
the CR and still leaves the LF which terminates a line.  I found a little 
perl to do the job of replacing the CR/LF with a literal \r

perl -p -e 's/\r\n/\\r/g' < cities.txt > cities_fixed.txt