A few questions about carriage returns (\r) - Mailing list pgsql-general

From Jon Lapham
Subject A few questions about carriage returns (\r)
Date
Msg-id 449173E9.9030109@jandr.org
Whole thread Raw
Responses Re: A few questions about carriage returns (\r)
List pgsql-general
I have a database that needs to work with TEXT input generated from a
heterogeneous environment (UNIX, Win, Mac).  As such, I have noticed
that equality comparisons of supposedly identical TEXT is failing due to
the different line terminators embedded in some TEXT fields.

So, as I understand it (please correct me if I'm wrong), UNIX uses a
"newline" (or \n), Mac uses "carriage return" (or \r) and Win/DOS uses
\r\n.

Looking into this issue has led me to a number of questions below...

Thanks for any help!
-Jon

=======================
1) Does anyone know why the "id" column is not visible for the final
select statement?  I guess a lone \r literally means to go to the
farthest position to the left... but it seems like a bug that it moves
past its column position.  Bug in psql?

test=> create table foo ( id serial, bar TEXT );
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
CREATE TABLE
test=> insert into foo (bar) VALUES ('Line 1\nLine 2');
INSERT 0 1
test=> insert into foo (bar) VALUES ('Line 1\r\nLine 2');
INSERT 0 1
test=> insert into foo (bar) VALUES ('Line 1\rLine 2');
INSERT 0 1
test=> select id,textcol from foo;
  id | textcol
----+---------
   1 | Line 1
Line 2
   2 | Line 1
Line 2
Line 2Line 1
(3 rows)

=======================
2) Is there a way to *view* the \n and \rs embedded in a TEXT field
using psql?

=======================
3) Is there a string function that is capable of replacing \r\n with \n?
   More generally, is there a string function capable of regular
expression replace?  (eg: perl and other languages have "=~
s/\r\n/\n/").  I imagine an SQL function that would work like this
fictional function:

SELECT re_replace('\r\n' IN bar USING '\n') FROM foo;

I see that "substring" works with REs, but I cannot for the life of me
figure out how to use substring equivalently to my fictional
re_replace() function above.





Extra information that may be useful:

test=> select version();
                                                          version

-------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 8.1.4 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 4.1.0 20060304 (Red Hat 4.1.0-3)
(1 row)


--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham  <lapham@jandr.org>                Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------


pgsql-general by date:

Previous
From: Stephane Bortzmeyer
Date:
Subject: Re: UTF8 problem
Next
From: Martijn van Oosterhout
Date:
Subject: Re: A few questions about carriage returns (\r)