WARNING: nonstandard use of escape in a string literal

In response to "Patrick M. Rutkowski" <rutski89@gmail.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/

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

In response to "Patrick M. Rutkowski" <rutski89@gmail.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@potentialtech.com> wrote:

In response to "Patrick M. Rutkowski" <rutski89@gmail.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/

Bill Moran wrote:

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.

and, naturally, this gets even more complicated and confusing when the
string is a literal in a C/Perl/etc program that has its OWN escaping
going on.

John R Pierce <pierce@hogranch.com> writes:

Bill Moran wrote:

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.

and, naturally, this gets even more complicated and confusing when the
string is a literal in a C/Perl/etc program that has its OWN escaping
going on.

... which is why we invented "dollar quoting". That's even less
standard than anything else, but it is tremendously useful when you'd
otherwise have to deal with multiple layers of escaping rules.

regards, tom lane

On Wed, Dec 23, 2009 at 3:52 PM, Bill Moran <wmoran@potentialtech.com> wrote:

In response to "Patrick M. Rutkowski" <rutski89@gmail.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@potentialtech.com> wrote:

In response to "Patrick M. Rutkowski" <rutski89@gmail.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

Patrick M. Rutkowski wrote:

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?

Precisely.

In other words: I'm already doing it right, no?

If you define "right" as "standard compliant", yes.
If you define "right" as "in accordance with the default
behaviour of PostgreSQL", then no.

Yours,
Laurenz Albe

On 23 Dec 2009, at 22:58, Patrick M. Rutkowski wrote:

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?

Yes, for this query you are. You may have other queries that rely on non-standard escaping though, and those would break if you toggle that setting.
Alternatively you can just turn off the warning (escape_string_warning).

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b333e33228059156120885!