PostgreSQL error:invalid Escape String Workaround _postgresql

I have a colleague today. After configuring PostgreSQL as the hive metabase, the following error message is encountered when running hive sql:

Copy Code code 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, ESCAPE ' \ ' Here, the quotation marks can only be one character, not two. The solution is simply to set the JDBC user's connection session property standard_conforming_strings to OFF.

Copy Code code as follows:

ALTER role xx in the DATABASE yy SET standard_conforming_strings to off;

Error reason description

The ' \ ' in the early PG normal string is treated as an escape character, so ' \ \ ' will eventually be considered ' \ '. However, this is not in accordance with the SQL standard, so in PG 9.1 and later, the normal string of \ will not be the task is the escape character, and E ' xx\x ' in the \ will be treated as an escape character. In order for the previous code to run in the new PG version, there is the standard_conforming_strings option, which defaults to on, means that strings are processed according to SQL standards, and when off, the string is processed according to the older version of PG.