PostgreSQL ERROR: invalid escape string solution, postgresqlescape

PostgreSQL ERROR: invalid escape string solution, postgresqlescape

After configuring PostgreSQL as a Hive metadatabase, a colleague encountered the following error when running Hive SQL:

Copy codeThe Code is as follows:
Caused by: MetaException (message: Got exception: org. apache. hadoop. hive. metastore. api. metaException javax. jdo. JDODataStoreException: Error executing JDOQL query "SELECT" THIS ". "NAME" AS NUCORDER0 FROM "DBS" "THIS" WHERE (LOWER ("THIS ". "NAME") LIKE? ESCAPE '\') order by NUCORDER0 ": ERROR: invalid escape string
Hint: Escape string must be empty or one character ..

The error message is clear. Here, ESCAPE '\' can only contain one character, not two \ characters \. The solution is simple. Set the connection session attribute standard_conforming_strings Of The JDBC user to off.

Copy codeThe Code is as follows:
Alter role xx in database yy SET standard_conforming_strings TO off;

Error cause description

'\' In an early PostgreSQL string is treated as an escape character, so '\' will eventually be considered '\'. However, this does not conform to the SQL standard. Therefore, in PostgreSQL 9.1 and later, the \ character in the common string will not be an escape character in the task, the \ In 'xx \ x' is considered as an escape character. To enable the previous code to run in the new PostgreSQL version, the standard_conforming_strings option is available. This option is on by default, indicating that the string is processed according to the SQL standard. When it is set to off, the string is processed according to the old version of PG.