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@xxxxxxxxxxxx\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@xxxxxxxxxxxx\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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
