Postgres over-escaping ?

[rolivares]

In order to get this sentence using WhereRaw:

SELECT * 
FROM "condition_tests" 
WHERE (
  "field_varchar_nullable" IS NOT NULL 
  AND unaccent(TRIM(regexp_replace(field_varchar_nullable, E'[\\n\\r]+', '', 'g' )))  ilike '%' || unaccent('le dijo')
)

I've used this string as raw sentence:

var sentence = @"unaccent(TRIM(regexp_replace(field_varchar_nullable, E'[\\n\\r]+', '', 'g' )))  ilike '%' || unaccent('le dijo')";
var query = new Query("condition_tests").WhereNotNull("field_varchar_nullable").WhereRaw(sentence);

I'm getting this expression (note the replace chars near E '"\n\r"+', '', 'g') )

SELECT
  *
FROM
  "condition_tests"
WHERE
  "field_varchar_nullable" IS NOT NULL
  AND unaccent(
    TRIM(
      regexp_replace(field_varchar_nullable, E '"\\n\\r"+', '', 'g')
    )
  ) ilike '%' || unaccent('le dijo')

If you compare results using MSSql and postgresql factory are different on chars "[" and "]".

How can I escape or acomplish the statement: E'[\n\r]+'

Thanks

UPDATE: note the space after "E" on expression => E '"\n\r"+', '', 'g') this is addded qhen symbol "[" is replaced When I've try to escape the symbols with "" that space continues appearing in consequence SQL does not work

[brgrz]

Similar issue here:

string query = "payload_json-> 'bid_TimeSeries' @> '[{\"flowDirectiondirection\":\"A02\"}]'";
q.WhereRaw(query);

I get the following SQL generated for postgres

WHERE payload_json-> 'bid_TimeSeries' @> '"""flowDirectiondirection":"A02"""'

but the expected output is

WHERE payload_json -> 'bid_TimeSeries' @> '[{"flowDirectiondirection":"A01"}]'

UPDATE:

was able to resolve this by using convention to escape [,],{,} charactes required by SqlKata like this

string jquery = $"payload_json -> 'bid_TimeSeries' @> '\\[\\{{\"flowDirectiondirection\":\"{item}\"\\}}\\]'";

(note the escaping of the backslash and escaping the special characters)

[shokurov]

We were able to resolve it with custom compiler, like this:

public class PostgresCompiler : global::SqlKata.Compilers.PostgresCompiler
{
    public PostgresCompiler(bool wrapIdentifiersInQuery)
    {
        WrapIdentifiersInQuery = wrapIdentifiersInQuery;
    }

    public bool WrapIdentifiersInQuery { get; }

    public override string WrapIdentifiers(string input)
    {
        return WrapIdentifiersInQuery
            ? input
                .ReplaceIdentifierUnlessEscaped(EscapeCharacter, "{", OpeningIdentifier)
                .ReplaceIdentifierUnlessEscaped(EscapeCharacter, "}", ClosingIdentifier)
                .ReplaceIdentifierUnlessEscaped(EscapeCharacter, "[", OpeningIdentifier)
                .ReplaceIdentifierUnlessEscaped(EscapeCharacter, "]", ClosingIdentifier)
            : input;
    }
}