Postgresql 9.5 Syntax error

Question

I perform the same sql scripts on two server with different postgres versions. The first one has postgres 9.4.4 (this works fine) and the other one 9.5 (this throws an exception) installed.

UPDATE archived_invoice SET encrypted_xml 
= encrypt(xml::bytea, 'MySuperSecretKey'::bytea, 'aes-ecb/pad:pkcs')

The exception:

Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type bytea
SQL Status:22P02

Are there any differences between these two postgresql versions?

Possibly you have standard_conforming_strings differing between systems, which leads to different interpretation of backslashes. Anyway you should use the convert_to() function to get bytes from text, as opposed to the cast to bytea. A cast does interpretation of escape sequences, which is completely off base here. — Daniel Vérité
– Daniel Vérité, Commented Mar 17, 2016 at 16:50

amphetamachine · Accepted Answer · 2016-03-17 15:03:53Z

7

PostgreSQL will throw an ERROR: invalid input syntax for type bytea at you if the text you're casting into a bytea contains an invalid escape sequence:

# select '\i is not a valid escape sequence'::bytea;
ERROR:  invalid input syntax for type bytea
LINE 1: select '\i is not a valid escape sequence'::bytea;

I'm guessing your "xml" column contains or may contain some backslashes. This is fine for XML and text columns, but presents an issue when casting to bytea.

You will need to escape the backslashes:

UPDATE archived_invoice SET encrypted_xml 
= encrypt(
    replace(xml, '\', '\\')::bytea,
    'MySuperSecretKey'::bytea,
    'aes-ecb/pad:pkcs'
)

answered Mar 17, 2016 at 15:03

amphetamachine

30.9k12 gold badges68 silver badges74 bronze badges

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

1 Comment

user2722077 Over a year ago

It works, thx! This is really strange. My script is working on windows but not on ubuntu. Why is this a problem?

Collectives™ on Stack Overflow

Postgresql 9.5 Syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related