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

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(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/
>