WARNING: nonstandard use of escape in a string lit

问题:

I have query to remove double space and convert it to single space.

UPDATE tablename SET name=trim(regexp_replace(name,'\s\s+',' ', 'g'));

It gives error:

WARNING: nonstandard use of escape in a string literal
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

回答1:

You are running an old version of Postgres with the setting escape_string_warning = on (default) and standard_conforming_strings = off (outdated!, default is on since Postgres 9.1). The manual:

escape_string_warning(boolean)

When on, a warning is issued if a backslash (\) appears in an ordinary string literal ('...' syntax) and standard_conforming_strings is off. The default is on. (...)

To just fix the syntax and get rid of the WARNING:

trim(regexp_replace(name, E'\\s\\s+', ' ', 'g'))

Proper solution: Upgrade to a current version of Postgres, or fix the outdated setting to standard_conforming_strings =on.
In modern Postgres, the expression you have is valid as is.

To be precise, \s is the class shorthand for [[:space:]], which includes any kind of white space (incl. tab, nbsp etc.). Your expression replaces any string of two or more white space char with a single space char. The expression to fit your description would be:

trim(regexp_replace(name,'  +', ' ', 'g'))

... which works regardless of version and above settings.

Related:

• Insert text with single quotes in PostgreSQL

• Order varchar string as numeric