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

Question

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.

I executed the query from pgAdmin and got the above mentioned error. — Muthu
– Muthu, Commented Sep 2, 2013 at 13:39
Please show us the full query. And did you verify that the pgAdmin version is the right one to work with Postgres 9.2? — user330315
– user330315, Commented Sep 2, 2013 at 13:43

Daniel Vérité · Accepted Answer · 2013-09-03 12:47:50Z

2

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

edited Sep 3, 2013 at 12:47

answered Sep 2, 2013 at 13:42

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Muthu Over a year ago

Thanks for your suggestion. Can you please tell how to set those compatibitiy flags to off.

Muthu Over a year ago

I have set 'standard_conforming_strings=off', 'escape_string_warning=off', 'backslash_quote=off' and 'bytea_output=escape' in the postgresql.conf file and restarted the server. I am still getting the same error when I try to insert values to bytea attributes. Any other workaround for this?

Daniel Vérité Over a year ago

@user2416545: backslash_quote=off is the reason. Note that I suggested to turn off the two others, not this one.

Muthu Over a year ago

Same error if I keep backslash_quote=on and backslash_quote=safe-encoding. I am not clear where I am going wrong.

Daniel Vérité Over a year ago

@user2416545: can you update the question with the error or lack thereof that happens on a simple select 'ab\'cd'::bytea;? Also you can double check the values of standard_conforming_strings and other settings in the client with show standard_conforming_strings.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related