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
– wildplasser, Commented Jul 10, 2013 at 22:24
If do not work wildplasser's solution, please try add another backslashes, for more information read this link. — Gaston Flores
– Gaston Flores, Commented Jul 10, 2013 at 23:24
@wildplasser I think that's a path, and they don't want it to be interpreted as escape sequences. — Craig Ringer
– Craig Ringer, Commented Jul 10, 2013 at 23:53

Craig Ringer · Accepted Answer · 2013-07-10 23:53:56Z

2

It should work if you enable standard_conforming_strings.

edited Jul 10, 2013 at 23:53

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Jul 10, 2013 at 23:45

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Craig Ringer Over a year ago

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.

Wolph Over a year ago

Thanks for the help @CraigRinger, always nice to see another 2ndQ member here ;)

Craig Ringer Over a year ago

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.

Wolph Over a year ago

Yes... I suppose I should really write a little bio ;)

Collectives™ on Stack Overflow

PostgreSQL: nonstandard use of escape string

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related