Re: WARNING: nonstandard use of escape in a string literal

In response to "Patrick M. Rutkowski" <rutski89(at)gmail(dot)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(at)potentialtech(dot)com> wrote:
> > In response to "Patrick M. Rutkowski" <rutski89(at)gmail(dot)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/