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

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/