CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Muthu
Muthu

Reputation: 3

Unable to insert values into bytea type attribute while migrating from postgres 7.4 to postgres 9.2

I am migrating an application from postgres 7.4 to postgres 9.2. The query which worked fine for inserting bytea type attribure values in postgres 7.4 is throwing PSQLException with the below error in postgres 9.2.

ERROR: syntax error at or near "\" LINE 1: ...07\000\000\001\002\000\000|\012\000\000\'\007\000...(Error is shown near the single quote)

*** Error ***

ERROR: syntax error at or near "\" SQL state: 42601 Character: 39081

I have read the postgres documentation about bytea_output which can be set to 'escape' to output the content of the attribute in escape format. It is also mentioned that bytea type attributes can accept both escape and hex format.

As the application was previously using postgres 7.4, we are using escape format. I wonder why this error is thrown if bytea can accept both escape and hex format in postgres 9.2. Please help in resolving this error.

Upvotes: 0

Views: 1576

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61696

bytea_output tells the format of bytea content in output columns, not when submitted as part of a SQL statement. As you guessed, it's probably not relevant here.

However, what may be relevant is the fact standard_conforming_strings has been set ON by default with PG 9.1 (I don't think it even existed in 7.4) so you can no longer use backslash to escape a single quote inside a literal.

See the compatibility flags and explanations in 9.2 doc: Previous PostgreSQL Versions, especially backslash_quote, escape_string_warning and standard_conforming_strings.

Short of fixing your app, the easy way out of the problem is to set standard_conforming_strings to off, and escape_string_warning to off if the amount of warnings in the logs is problematic (actually this warning is mostly useful in the process of fixing your app to standard strings conformance).

This can be done globally in the postgresql.conf file, which is located in the data directory. See Setting Parameters in the doc.

standard_conforming_strings=off  
escape_string_warning=off

Upvotes: 2

Related Questions