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(dot)halbritter(at)zweiernet(dot)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(dot)halbritter(at)zweiernet(dot)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(at)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(dot)klaver(at)aklaver(dot)com