Thread: WARNING: nonstandard use of escape in a string literal
I just ran something like: ============================================= UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' ============================================= I got the following warnings/hints as a result: ============================================= WARNING: nonstandard use of escape in a string literal HINT: USE the escape string syntax for escapes, e.g., E'\r\n'. UPDATE 500 ============================================= Oddly it actually updated, and did just exactly what I wanted! :-) So what am I to make of those weird hints and warning?
In response to "Patrick M. Rutkowski" <rutski89@gmail.com>: > I just ran something like: > ============================================= > UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' > ============================================= > > > I got the following warnings/hints as a result: > ============================================= > WARNING: nonstandard use of escape in a string literal > HINT: USE the escape string syntax for escapes, e.g., E'\r\n'. > UPDATE 500 > ============================================= > > > Oddly it actually updated, and did just exactly what I wanted! :-) > > So what am I to make of those weird hints and warning? The SQL spec says that inside '', strings are to be interpreted exactly, except for the string '', which is converted to '. Obviously, 99% of the world thinks they should be able to use \ to escape special characters (like \n and \t). PostgreSQL has historically supported the more common use and not been strict to the standard. This is changing. Newer versions of PG will (someday) no longer support that syntax, and the warnings are alerting you to code that will stop working when that happens. In any event, you can work around this using the string escape syntax (i.e. WHERE colname ~ E'^\s*$') and the official documentation is here: http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
No, that doesn't sound right. I'm not trying to insert a literal '\s' or anything (whatever the heck that might mean). The sequence '\s' is to be interpreted by the ~ regular expression operator, isn't it? I would imagine that I would want the sequence of BACKSLASH + LETTER_S to go through to the ~ operator untouched. I don't _want_ it to do any escaping, so the E prefix feels wrong, no? I'm still confused, -Patrick On Wed, Dec 23, 2009 at 3:32 PM, Bill Moran <wmoran@potentialtech.com> wrote: > In response to "Patrick M. Rutkowski" <rutski89@gmail.com>: > >> I just ran something like: >> ============================================= >> UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' >> ============================================= >> >> >> I got the following warnings/hints as a result: >> ============================================= >> WARNING: nonstandard use of escape in a string literal >> HINT: USE the escape string syntax for escapes, e.g., E'\r\n'. >> UPDATE 500 >> ============================================= >> >> >> Oddly it actually updated, and did just exactly what I wanted! :-) >> >> So what am I to make of those weird hints and warning? > > The SQL spec says that inside '', strings are to be interpreted exactly, > except for the string '', which is converted to '. > > Obviously, 99% of the world thinks they should be able to use \ to > escape special characters (like \n and \t). PostgreSQL has historically > supported the more common use and not been strict to the standard. > > This is changing. Newer versions of PG will (someday) no longer support > that syntax, and the warnings are alerting you to code that will stop > working when that happens. > > In any event, you can work around this using the string escape syntax > (i.e. WHERE colname ~ E'^\s*$') and the official documentation is here: > http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ >
In response to "Patrick M. Rutkowski" <rutski89@gmail.com>: > No, that doesn't sound right. > > I'm not trying to insert a literal '\s' or anything (whatever the heck > that might mean). The sequence '\s' is to be interpreted by the ~ > regular expression operator, isn't it? I would imagine that I would > want the sequence of BACKSLASH + LETTER_S to go through to the ~ > operator untouched. I don't _want_ it to do any escaping, so the E > prefix feels wrong, no? > > I'm still confused, You need to spend some quality time with the documentation. Seriously, the issue _is_ confusing, but the docs explain it all, if you take the time to read all of it. To directly answer your question, \s is not a recognized escape sequence, so PG passes it unchanged. However, if you were trying to pass a \f, you would need to escape the \, like this '\\f', otherwise the \f would be converted to a form feed before LIKE ever saw it. As I said, this behaviour is expected to change at some point in the future, although I don't know that an exact release has been picked yet. Until that time, you can control the behavior with configuration settings in your postgresql.conf. standard_conforming_strings is the most dramatic example. And please don't top-post. > On Wed, Dec 23, 2009 at 3:32 PM, Bill Moran <wmoran@potentialtech.com> wrote: > > In response to "Patrick M. Rutkowski" <rutski89@gmail.com>: > > > >> I just ran something like: > >> ============================================= > >> UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' > >> ============================================= > >> > >> > >> I got the following warnings/hints as a result: > >> ============================================= > >> WARNING: nonstandard use of escape in a string literal > >> HINT: USE the escape string syntax for escapes, e.g., E'\r\n'. > >> UPDATE 500 > >> ============================================= > >> > >> > >> Oddly it actually updated, and did just exactly what I wanted! :-) > >> > >> So what am I to make of those weird hints and warning? > > > > The SQL spec says that inside '', strings are to be interpreted exactly, > > except for the string '', which is converted to '. > > > > Obviously, 99% of the world thinks they should be able to use \ to > > escape special characters (like \n and \t). PostgreSQL has historically > > supported the more common use and not been strict to the standard. > > > > This is changing. Newer versions of PG will (someday) no longer support > > that syntax, and the warnings are alerting you to code that will stop > > working when that happens. > > > > In any event, you can work around this using the string escape syntax > > (i.e. WHERE colname ~ E'^\s*$') and the official documentation is here: > > http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Bill Moran wrote: > You need to spend some quality time with the documentation. Seriously, > the issue _is_ confusing, but the docs explain it all, if you take the > time to read all of it. > > To directly answer your question, \s is not a recognized escape sequence, > so PG passes it unchanged. > > However, if you were trying to pass a \f, you would need to escape the \, > like this '\\f', otherwise the \f would be converted to a form feed before > LIKE ever saw it. > and, naturally, this gets even more complicated and confusing when the string is a literal in a C/Perl/etc program that has its OWN escaping going on.
John R Pierce <pierce@hogranch.com> writes:
> Bill Moran wrote:
>> You need to spend some quality time with the documentation. Seriously,
>> the issue _is_ confusing, but the docs explain it all, if you take the
>> time to read all of it.
> and, naturally, this gets even more complicated and confusing when the
> string is a literal in a C/Perl/etc program that has its OWN escaping
> going on.
... which is why we invented "dollar quoting". That's even less
standard than anything else, but it is tremendously useful when you'd
otherwise have to deal with multiple layers of escaping rules.
regards, tom lane
On Wed, Dec 23, 2009 at 3:52 PM, Bill Moran <wmoran@potentialtech.com> wrote: > In response to "Patrick M. Rutkowski" <rutski89@gmail.com>: > >> No, that doesn't sound right. >> >> I'm not trying to insert a literal '\s' or anything (whatever the heck >> that might mean). The sequence '\s' is to be interpreted by the ~ >> regular expression operator, isn't it? I would imagine that I would >> want the sequence of BACKSLASH + LETTER_S to go through to the ~ >> operator untouched. I don't _want_ it to do any escaping, so the E >> prefix feels wrong, no? >> >> I'm still confused, > > You need to spend some quality time with the documentation. Seriously, > the issue _is_ confusing, but the docs explain it all, if you take the > time to read all of it. > > To directly answer your question, \s is not a recognized escape sequence, > so PG passes it unchanged. > > However, if you were trying to pass a \f, you would need to escape the \, > like this '\\f', otherwise the \f would be converted to a form feed before > LIKE ever saw it. > > As I said, this behaviour is expected to change at some point in the future, > although I don't know that an exact release has been picked yet. Until that > time, you can control the behavior with configuration settings in your > postgresql.conf. standard_conforming_strings is the most dramatic example. > > And please don't top-post. > >> On Wed, Dec 23, 2009 at 3:32 PM, Bill Moran <wmoran@potentialtech.com> wrote: >> > In response to "Patrick M. Rutkowski" <rutski89@gmail.com>: >> > >> >> I just ran something like: >> >> ============================================= >> >> UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' >> >> ============================================= >> >> >> >> >> >> I got the following warnings/hints as a result: >> >> ============================================= >> >> WARNING: nonstandard use of escape in a string literal >> >> HINT: USE the escape string syntax for escapes, e.g., E'\r\n'. >> >> UPDATE 500 >> >> ============================================= >> >> >> >> >> >> Oddly it actually updated, and did just exactly what I wanted! :-) >> >> >> >> So what am I to make of those weird hints and warning? >> > >> > The SQL spec says that inside '', strings are to be interpreted exactly, >> > except for the string '', which is converted to '. >> > >> > Obviously, 99% of the world thinks they should be able to use \ to >> > escape special characters (like \n and \t). PostgreSQL has historically >> > supported the more common use and not been strict to the standard. >> > >> > This is changing. Newer versions of PG will (someday) no longer support >> > that syntax, and the warnings are alerting you to code that will stop >> > working when that happens. >> > >> > In any event, you can work around this using the string escape syntax >> > (i.e. WHERE colname ~ E'^\s*$') and the official documentation is here: >> > http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS > > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > In that case, let me put it this way: Is the query UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' already all correct and standard conforming. Such that all I need to do is turn on standard_conforming_strings to have it stop complaining at me? In other words: I'm already doing it right, no? -Patrick
Patrick M. Rutkowski wrote: > Is the query > UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' > > already all correct and standard conforming. Such that all I need to > do is turn on standard_conforming_strings to have it stop complaining > at me? Precisely. > In other words: I'm already doing it right, no? If you define "right" as "standard compliant", yes. If you define "right" as "in accordance with the default behaviour of PostgreSQL", then no. Yours, Laurenz Albe
On 23 Dec 2009, at 22:58, Patrick M. Rutkowski wrote: > In that case, let me put it this way: > > Is the query > UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' > > already all correct and standard conforming. Such that all I need to > do is turn on standard_conforming_strings to have it stop complaining > at me? > > In other words: I'm already doing it right, no? Yes, for this query you are. You may have other queries that rely on non-standard escaping though, and those would breakif you toggle that setting. Alternatively you can just turn off the warning (escape_string_warning). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b333e33228059156120885!