Re: Question on replace function [solved]

On 09/25/2016 05:45 AM, Charles Clavadetscher wrote:
> Hi Rob
>
> On 09/25/2016 01:39 PM, rob stone wrote:
>>
>> On Sun, 2016-09-25 at 10:29 +0200, Charles Clavadetscher wrote:
>>> 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  |
>>>>                       |
>>> +-----------------------+
>>>
>>>
>>
>>
>>
>> Tested this on 9.6beta3 on a test database and it appears to work fine.
>>
>> Inserted one row.
>>
>> dinkumerp=> select * from contacts;
>> LOG:  duration: 0.571 ms  statement: select * from contacts;
>>  contact_id |            changed            | del | name | email |
>> firstname | s
>> urname
>> |                              vcard                              |
>> words
>>  | user_id
>> ------------+-------------------------------+-----+------+-------+-----
>> ------+--
>> -------+---------------------------------------------------------------
>> --+------
>> -+---------
>>           1 | 2016-09-25 21:30:54.788442+10 |   0
>> |      |       |           |
>>        | 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)
>>
>> Replace select.
>>
>> dinkumerp=> select replace(vcard,'\r\\r',E'\r\n') from contacts;
>> LOG:  duration: 0.400 ms  statement: select
>> replace(vcard,'\r\\r',E'\r\n') from contacts;
>>                 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)
>>
>>
>> HTH,
>> Rob
>>
>
> Thank you. Unfortunately this did not help. But it was a confirmation
> that there must be a difference in what I see in the console and what is
> actually stored in the DB.
>
> I found a way to check that and with that a solution to my problem.
>
> First I created a table:
>
> create table test (txt text);
>
> Then I inserted two rows:
> One using a value from the table:
>
> insert into test values ((select vcard from contacts limit 1));
>
> And one using the string as it appears in the console:
>
> insert into test values
> ('BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:Halbritter;Ursula;;;\r\\rFN:Ursula
>
Halbritter\r\\rEMAIL;TYPE=INTERNET;TYPE=HOME:ursula.halbritter@zweiernet.ch\r\\rADR;TYPE=home:;;strasse;schwerzenbach;;;ooooooooooooooooooooooooooo\r\\rEND:VCARD');

Short version try the above as:

insert into test values
(E'BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:Halbritter;Ursula;;;\r\\rFN:Ursula

Halbritter\r\\rEMAIL;TYPE=INTERNET;TYPE=HOME:ursula.halbritter@zweiernet.ch\r\\rADR;TYPE=home:;;strasse;schwerzenbach;;;ooooooooooooooooooooooooooo\r\\rEND:VCARD');



Long version:

https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html

4.1.2.2. String Constants with C-style Escape

or

4.1.2.4. Dollar-quoted String Constants

>
> In the console they look exactly the same:
>
> roundcubemail2=> select * from test;
> txt
>
------------------------------------------------------------------------------------------------------------------------------------------
>
>  BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
> ;;;\r\\rFN:Firstname
> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD
>  BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
> ;;;\r\\rFN:Firstname
> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD
>
> Next, I wrote the content of the table to a file:
>
> \copy test to test.txt
>
> And compared the two rows in the file:
>
> charles@as11:~$ cat test.txt
> BEGIN:VCARD\r\\\rVERSION:3.0\r\\\rN:;Firstname Lastname
> ;;;\r\\\rFN:Firstname
> Lastname\r\\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\\rEND:VCARD
> BEGIN:VCARD\\r\\\\rVERSION:3.0\\r\\\\rN:;Firstname Lastname
> ;;;\\r\\\\rFN:Firstname
> Lastname\\r\\\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\\r\\\\rEND:VCARD
>
> They differ. The string from the original table contains '\r\\\r' while
> the string inserted as such contains '\\r\\\\r' Based on that I could
> eventually transform the content of the fields:
>
> roundcubemail=> update contacts set vcard =
> replace(vcard,E'\r\\\r',E'\r\n') ;
> UPDATE 623
>
> SELECT vcard FROM contacts LIMIT 1;
>                  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)
>
> Honestly I still don't understand why this happened this way.
>
> Charles
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com