PostgreSQL ERROR: invalid escape string solution, postgresqlescape

Source: Internet
Author: User
Tags postgresql version

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.




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.