Обсуждение: Backslashes in data in version 8.1.2

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

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

> 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

> 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.

Nope, it's not a locale issue, it works on the test system using the C
locale as well as the default locale.

I though I had the backslash issue under control in my PHP app, whatever
changed is apparently affecting both Postgres and PHP.

Any ideas?  Some kind of library issue maybe, such as readline?
--
Mike Nolan