On Wed, Dec 23, 2009 at 3:52 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote: > 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/ > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
