Thread: 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