Question on replace function - Mailing list pgsql-general

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  |
|                       |
+-----------------------+