Search Postgresql Archives

Re: Question on replace function [solved]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxx\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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux