PostgreSQL ERROR: invalid escape string solution

Today a colleague encountered the following error message while running Hive SQL after configuring PostgreSQL as Hive metadata:

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 is only one character in the quote, not two \. The solution is simply to set the connection session property standard_conforming_strings for the JDBC user to off.

ALTER ROLE xx IN DATABASE yy SET standard_conforming_strings TO off;

error causes

The '\' in the early PG normal string was treated as an escape character, so '\' would eventually be considered '\'. However, this does not conform to SQL, so \ in normal strings will not be treated as an escape character, while \ in E'xx\x' will be treated as an escape character after PG 9.1. To make the previous code run in the new PG version, there is an option called standard_conforming_strings, which defaults to on to handle strings as SQL and, when set to off, as PG's old version.