In response to "Patrick M. Rutkowski" <rutski89@xxxxxxxxx>: > 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@xxxxxxxxxxxxxxxxx> wrote: > > In response to "Patrick M. Rutkowski" <rutski89@xxxxxxxxx>: > > > >> 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
