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

On Wed, Dec 23, 2009 at 3:52 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> 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/
>

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