PostgreSQL: nonstandard use of escape string

Question

I have a PostgreSQL 8.4 database that is being queried by an application that is outside of my control. Queries such as the following are throwing warnings but are working...

SELECT "tagname","tagindex","tagtype","tagdatatype"  FROM "tagtable" WHERE "tagname" = 'Lift_Stations\07\ETMs\Generator_ETM'

However, the same query for stations 08 and 09 are failing...

SELECT "tagname","tagindex","tagtype","tagdatatype"  FROM "tagtable" WHERE "tagname" = 'Lift_Stations\08\ETMs\Generator_ETM'

WARNING: nonstandard use of escape in a string literal LINE 2: ...,"tagdatatype" FROM "tagtable" WHERE "tagname" = 'Lift_Stat... ^ HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.

ERROR: invalid byte sequence for encoding "UTF8": 0x00 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

*** Error ***

ERROR: invalid byte sequence for encoding "UTF8": 0x00 SQL state: 22021 Hint: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

I know the problem is incorrect escaping, but given the fact that 08 and 09 are the only ones not working, I'm hoping someone might have a bright idea on how to work around this.

Thanks!

`WHERE "tagname" = E'Lift_Stations\08\ETMs\Generator_ETM'` could help. — wildplasser, Jul 10 '13 at 22:24
If do not work wildplasser's solution, please try add another backslashes, for more information read [this link](http://stackoverflow.com/a/943/1563878). — Gaston Flores, Jul 10 '13 at 23:24
@wildplasser I think that's a path, and they don't *want* it to be interpreted as escape sequences. — Craig Ringer, Jul 10 '13 at 23:53

score 2 · Accepted Answer · edited Jul 10 '13 at 23:53

2

It should work if you enable standard_conforming_strings.

edited Jul 10 '13 at 23:53

Craig Ringer

307,061
76
688
778

answered Jul 10 '13 at 23:45

Wolph

78,177
11
137
148

1

To elaborate on this correct but brief answer: Older PostgreSQL versions used to let you write C-style escapes like `\n` in strings. This has been turned off by default in newer versions; this warning tells you that you're on a version where it still happens. – Craig Ringer Jul 10 '13 at 23:55
Thanks for the help @CraigRinger, always nice to see another 2ndQ member here ;) – Wolph Jul 11 '13 at 00:04
I didn't realise you were on the team. Stealthy. Amsterdam... wonder who that could be ;-) . Really supposed to have mention of 2ndQ in your profile if doing Pg related community stuff IIRC. – Craig Ringer Jul 11 '13 at 01:19
Yes... I suppose I should really write a little bio ;) – Wolph Jul 11 '13 at 09:32

PostgreSQL: nonstandard use of escape string

1 Answers1