Re: BUG #15069: group by after regexp_replace

On Thu, Feb 15, 2018 at 8:14 AM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15069
> Logged by: Ilhwan Ko
> Email address: koglep(at)gmail(dot)com
> PostgreSQL version: 9.6.7
> Operating system: macOS 10.12
> Description:
>
> select upper(regexp_replace(b, '\\s+', '')) as keyword, sum(c)
> from test_t
> group by upper(regexp_replace(b, '\\s+', ''));
>
> I expected to get the same results regarding to above four queries.
> However, they were different.
>
>
​Not a bug - you mis-understand string literal syntax and escaping. What
you are asking to replace is "a backslash followed by one or more "s"es.

see:
https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

If you want to leave the literal as-is you need to write:

E'\\s+'

The way I prefer is to keep the "E" ​omitted and write:

'\s+'

Without the "E" the backslash is not an escape character in a PostgreSQL
literal and so the backslash in the regex doesn't need to be protected. By
protecting it you are actually protecting the backslash in front of the "s"
thus causing it to become two separate "symbols", "\" and "s" - and the +
then applies to the literal "s".

David J.