Question on replace function

Hello

I am using PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by gcc
(Debian 4.7.2-5) 4.7.2, 64-bit

I imported data from a MariaDB table into PostgreSQL and noticed that
the content of a field was not correct, but I was not able to change it.
The field is called vcard and is of datatye text.

The structure of the table:

roundcubemail=# \d contacts
Tabelle „public.contacts“
Spalte | Typ |
Attribute
------------+--------------------------+----------------------------------------------------------------
contact_id | integer | not null Vorgabewert
nextval(('contacts_seq'::text)::regclass)
changed | timestamp with time zone | not null Vorgabewert now()
del | smallint | not null Vorgabewert 0
name | character varying(128) | not null Vorgabewert
''::character varying
email | text | not null Vorgabewert ''::text
firstname | character varying(128) | not null Vorgabewert
''::character varying
surname | character varying(128) | not null Vorgabewert
''::character varying
vcard | text |
words | text |
user_id | integer | not null

The content of vcard looks as follows (replaced real names with
placeholders):

BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
;;;\r\\rFN:Firstname
Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD

My target is to replace all occurences of '\r\\r' with E'\r\n' to comply
with RFC 6350.

I tried using the function replace and I am faced with a strange
behaviour. If I use the function with a string as shown above I get the
expected result:

elect replace('BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
;;;\r\\rFN:Firstname
Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD','\r\\r',E'\r\n')
;

replace
----------------------------------------
BEGIN:VCARD\r +
VERSION:3.0\r +
N:;Firstname Lastname ;;;\r +
FN:Firstname Lastname\r +
EMAIL;TYPE=INTERNET;TYPE=OTHER:email\r+
END:VCARD
(1 row)

However, if I use the function on the vcard field nothing is replaced:

select replace(vcard,'\r\\r',E'\r\n') from contacts;

replace
------------------------------------------------------------------------------------------------------------------------------------------
BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
;;;\r\\rFN:Firstname
Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD
(1 row)

Does anybody have an idea what I am doing wrong?
Thank you for your help.

Charles

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+-----------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| PostgreSQL 1996-2016 |
| 20 Years of Success |
| |
+-----------------------+