Postgres Pro
Downloads
Home   >   mailing lists

Thread: "invalid input syntax for type bytea"

"invalid input syntax for type bytea"

From
Alan Millington
Date:
I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My database is UTF8. My program code is written in Python, and to interface to Postgres I use mxODBC 3.0 and the PostgreSQL Unicode driver PSQLODBCW.DLL version 8.01.02.00 dated 31/01/2006.

I recently hit an error when trying to insert into a bytea column. The following is the first part of what appears in the Postgres log:

2011-08-25 14:42:40 BST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
2011-08-25 14:42:40 BST ERROR:  invalid input syntax for type bytea at character 75
2011-08-25 14:42:40 BST STATEMENT:  insert into FAMILY_DATA (family_id, seq_num, family_pod) values (177, 20, '\\200\\002\\135q\\001\\050cperson\\012Person\\012q\\002\\051\\201q\\003\\175q\\004\\050U\\006\\137namesq\\005cperson\\012\\137Names\\012q\\006\\051\\201q\\007cperson\\012\\137Name\\012q\\010\\051\\201q\\011\\050\\135q\\012X\\003\\000\\000\\00017\\052q\\013a\\135q\\014X\\002\\000\\000\\000ABq\\015ae\\175q\\016U\\006parentq\\017h\\007sba\\175q\\020U\\006personq\\021h\\003sbU\\021nationality\\137eventq\\022cperson\\012Dictof\\137Nationality\\137Event\\012q\\023\\051\\201q\\024X\\004\\000\\000\\000UKBSq\\025\\175q\\026\\051cperson\\012Listof\\137Nationality\\137Event\\012q\\027\\051\\201q\\030\\050cperson\\012Nationality\\137Event\\012q\\031\\051\\201q\\032\\175q\\033\\050U\\015contra\\137eventsq\\034\\051U\\006parentq\\035h\\030U\\004bvosq\\036c\\137\\137builtin\\137\\137\\012set\\012q\\037\\135q U\\002ATq\\041a\\205Rq\\042U\\007cfflagsq\\043\\051U\\006effectq\\044U\\001AU\\005cinfoq\\045ccinfo\\012BCInfo

(I omit the rest, but there is a close quote and a close parenthesis at the end.)

If I have counted right, character 75 is the '7' in '\\175', which looks to be a valid octal value.

The statement as shown in the log is generated by mxODBC, not by me, so if there is something wrong with it I shall have to take the matter up with eGenix. Before I do so it would be useful to know what is wrong with the statement.

I had to tweak my own code when I upgraded from Postgres 8.1 to 8.4, but since then I have inserted 5092 rows with an average binary data length of 40,000 bytes. This is the only insert that has failed.

Re: "invalid input syntax for type bytea"

From
Tom Lane
Date:
Alan Millington <admillington@yahoo.co.uk> writes:
> I recently hit an error when trying to insert into a bytea column. The following is the first part of what appears in
thePostgres log: 
> 2011-08-25 14:42:40 BST HINT: �Use the escape string syntax for backslashes, e.g., E'\\'.2011-08-25 14:42:40 BST
ERROR:�invalid input syntax for type bytea at character 752011-08-25 14:42:40 BST STATEMENT: �insert into FAMILY_DATA
(family_id,seq_num, family_pod) values (177, 20, 
>
'\\200\\002\\135q\\001\\050cperson\\012Person\\012q\\002\\051\\201q\\003\\175q\\004\\050U\\006\\137namesq\\005cperson\\012\\137Names\\012q\\006\\051\\201q\\007cperson\\012\\137Name\\012q\\010\\051\\201q\\011\\050\\135q\\012X\\003\\000\\000\\00017\\052q\\013a\\135q\\014X\\002\\000\\000\\000ABq\\015ae\\175q\\016U\\006parentq\\017h\\007sba\\175q\\020U\\006personq\\021h\\003sbU\\021nationality\\137eventq\\022cperson\\012Dictof\\137Nationality\\137Event\\012q\\023\\051\\201q\\024X\\004\\000\\000\\000UKBSq\\025\\175q\\026\\051cperson\\012Listof\\137Nationality\\137Event\\012q\\027\\051\\201q\\030\\050cperson\\012Nationality\\137Event\\012q\\031\\051\\201q\\032\\175q\\033\\050U\\015contra\\137eventsq\\034\\051U\\006parentq\\035h\\030U\\004bvosq\\036c\\137\\137builtin\\137\\137\\012set\\012q\\037\\135q
U\\002ATq\\041a\\205Rq\\042U\\007cfflagsq\\043\\051U\\006effectq\\044U\\001AU\\005cinfoq\\045ccinfo\\012BCInfo
> (I omit the rest, but there is a close quote and a close parenthesis at the end.)

Well, the part you showed us is perfectly valid bytea data, so the
problem is somewhere in what you omitted.

> If I have counted right, character 75 is the '7' in '\\175', which looks to be a valid octal value.

Unfortunately, that number is just a pointer to the start of the bytea
literal within the statement, so it's not very helpful here :-(.  What
you need to look for is a backslash that's not followed by either a
second backslash or octal digits.

            regards, tom lane

Re: "invalid input syntax for type bytea"

From
John R Pierce
Date:
On 08/31/11 9:02 AM, Alan Millington wrote:
> I am running Postgres 8.4.1 on Windows XP Pro...

8.4 is currently up to 8.4.8 and you really should upgrade.    8.4.1 is
about 2 years old, and there's a long list of bugs fixed in the later
8.4 updates, see the release notes for 8.4.2 through 8.4.8 for the gory
details,
http://www.postgresql.org/docs/8.4/static/release.html


Any particular reason you're using ODBC and not a PostgreSQL python
binding like Psycopg ?   I find the more layers of abstraction between
your code and the outside world, the more obscure the bugs are, and the
harder they are to track down.




--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Re: "invalid input syntax for type bytea"

From
Alan Millington
Date:
Thank you for that. Sure enough, at character 36202 I have found "\\ 51". I assume that the space should be a zero. That appears to be the only error. How odd!


--- On Wed, 31/8/11, Tom Lane <tgl@sss.pgh.pa.us> wrote:

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [GENERAL] "invalid input syntax for type bytea"
To: "Alan Millington" <admillington@yahoo.co.uk>
Cc: "Postgres general mailing list" <pgsql-general@postgresql.org>
Date: Wednesday, 31 August, 2011, 18:24

Alan Millington <admillington@yahoo.co.uk> writes:
> I recently hit an error when trying to insert into a bytea column. The following is the first part of what appears in the Postgres log:
> 2011-08-25 14:42:40 BST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.2011-08-25 14:42:40 BST ERROR:  invalid input syntax for type bytea at character 752011-08-25 14:42:40 BST STATEMENT:  insert into FAMILY_DATA (family_id, seq_num, family_pod) values (177, 20,
>  '\\200\\002\\135q\\001\\050cperson\\012Person\\012q\\002\\051\\201q\\003\\175q\\004\\050U\\006\\137namesq\\005cperson\\012\\137Names\\012q\\006\\051\\201q\\007cperson\\012\\137Name\\012q\\010\\051\\201q\\011\\050\\135q\\012X\\003\\000\\000\\00017\\052q\\013a\\135q\\014X\\002\\000\\000\\000ABq\\015ae\\175q\\016U\\006parentq\\017h\\007sba\\175q\\020U\\006personq\\021h\\003sbU\\021nationality\\137eventq\\022cperson\\012Dictof\\137Nationality\\137Event\\012q\\023\\051\\201q\\024X\\004\\000\\000\\000UKBSq\\025\\175q\\026\\051cperson\\012Listof\\137Nationality\\137Event\\012q\\027\\051\\201q\\030\\050cperson\\012Nationality\\137Event\\012q\\031\\051\\201q\\032\\175q\\033\\050U\\015contra\\137eventsq\\034\\051U\\006parentq\\035h\\030U\\004bvosq\\036c\\137\\137builtin\\137\\137\\012set\\012q\\037\\135q U\\002ATq\\041a\\205Rq\\042U\\007cfflagsq\\043\\051U\\006effectq\\044U\\001AU\\005cinfoq\\045ccinfo\\012BCInfo
> (I omit the rest, but there is a close quote and a close parenthesis at the end.)

Well, the part you showed us is perfectly valid bytea data, so the
problem is somewhere in what you omitted.

> If I have counted right, character 75 is the '7' in '\\175', which looks to be a valid octal value.

Unfortunately, that number is just a pointer to the start of the bytea
literal within the statement, so it's not very helpful here :-(.  What
you need to look for is a backslash that's not followed by either a
second backslash or octal digits.

            regards, tom lane