When I moved up to 8.1.2 one of my PHP programs appears to be broken,
I am getting backslashes in my data that I don't want.

Investigating further, I have found some inconsistencies in how verion
8.1.2 handles data with backslashes in it:

uscf=> \d backtest;
Table "mikenolan.backtest"
Column | Type | Modifiers
--------+------+-----------
field | text |

uscf=> insert into backtest values ('ABCDEFG');
insert into backtest values ('ABCDEFG');
INSERT 417194901 1

uscf=> insert into backtest values (E'ABC\\DEFG');
insert into backtest values (E'ABC\\DEFG');
INSERT 417194902 1

uscf=> select * from backtest;
select * from backtest;
field
----------
ABCDEFG
ABC\DEFG
(2 rows)

uscf=> select * from backtest where field like E'%\\%';
select * from backtest where field like E'%\\%';
field
-------
(0 rows)

select * from backtest where field like E'%\\134%'

field
-------
(0 rows)

uscf=> select * from backtest where field ~ E'\\';
select * from backtest where field ~ E'\\';
ERROR: invalid regular expression: invalid escape \ sequence

uscf=> select * from backtest where field ~ E'\\134';
select * from backtest where field ~ E'\\134';
field
----------
ABC\DEFG
(1 row)

So far the only way I have found to change data with backslashes in it
is something like the following:

update backtest
set field = replace(field,'\\','')
where
field ~ E'\\134';
UPDATE 1

uscf=> select * from backtest;
select * from backtest;
field
---------
ABCDEFG
ABCDEFG
(2 rows)
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Mike Nolan <nolan@gw.tssi.com> writes:
> When I moved up to 8.1.2 one of my PHP programs appears to be broken,
> I am getting backslashes in my data that I don't want.
> Investigating further, I have found some inconsistencies in how verion
> 8.1.2 handles data with backslashes in it:

This has not changed from prior versions. It looks like you are
neglecting to allow for the fact that backslash is an escape character
both at the string-literal level and at the regex-pattern level.
Therefore you must write twice as many backslashes as you normally
would write in a regex pattern. In particular, '\\\\' to match a
literal backslash.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> This has not changed from prior versions. It looks like you are
> neglecting to allow for the fact that backslash is an escape character
> both at the string-literal level and at the regex-pattern level.
> Therefore you must write twice as many backslashes as you normally
> would write in a regex pattern. In particular, '\\\\' to match a
> literal backslash.

Something must have changed, Tom, because neither of the following work
on the system where I now have 8.1.2 but do work on another system
running 7.4.5, and in both 8.0.2 and 8.1.2 on a third system:

select * from backtest where field ~ '\\\\';
field
-------
(0 rows)

select * from backtest where field like '%\\\\%';
field
-------
(0 rows)

Could this be a locale issue? The one where it does not work uses the C
locale, the others use the default locale, en_US.UTF-8.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org