WARNING: nonstandard use of escape in a string literal

Question

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'.

This is probably an old version of PostgreSQL, can you tell us which one? — Laurenz Albe, Sep 23 '16 at 12:31
That's your version of pgAdmin most likely. What's your version of Postgres? `SELECT version();` — Erwin Brandstetter, Sep 23 '16 at 12:37
@ErwinBrandstetter PostgreSQL 9.0.18 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit — Dhairya Lakhera, Sep 26 '16 at 06:40

score 4 · Accepted Answer · edited May 23 '17 at 12:30

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.

If using PHP you can use pg_escape_literal (NOT pg_escape_string) which will delimit and escape your strings correctly according to your postgres settings. http://php.net/manual/en/function.pg-escape-literal.php — Pascal_dher, May 28 '18 at 07:33

score 0 · Answer 2 · edited Feb 19 '21 at 00:51

0

The below should also work.

trim(regexp_replace(name,E'\s+','\s{2}',

edited Feb 19 '21 at 00:51

Muhammad Dyas Yaskur

6,914
10
48
73

answered Feb 15 '21 at 19:57

Neale Chaudhury

1
4

Please have a read of the [formatting help page](https://stackoverflow.com/editing-help) to improve the formatting in your answer, and also check out [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) to improve your answer. – costaparas Feb 16 '21 at 05:36

WARNING: nonstandard use of escape in a string literal

2 Answers2

Linked