Thread: Question on replace function
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 |
| |
+-----------------------+
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