Re: Question on replace function [solved] - Mailing list pgsql-general

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');

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