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/
***-*--*----*-------*------------*--------------------*---------------
