Thread: BYTEA: PostgreSQL 9.1 vs 9.3

Carel Combrink <carel.combrink@gmail.com> writes:
> I am using a PostgreSQL 9.1 server and the following query is perfectly
> valid for a BYTEA type column:
> INSERT INTO files VALUES(18,'\x536f6d6520746573742062797465206172726179');

> But for a server running version 9.3 the above query fails with the
> following error:
> ERROR:  array value must start with "{" or dimension information
> LINE 1: insert into files values(18,'\x504b030414000600080000002100e...

Works for me:

$ psql
psql (9.3.2)
Type "help" for help.

regression=# create table files (f1 int, f2 bytea);
CREATE TABLE
regression=# INSERT INTO files VALUES(18,'\x536f6d6520746573742062797465206172726179');
INSERT 0 1
regression=# select * from files;
 f1 |                     f2
----+--------------------------------------------
 18 | \x536f6d6520746573742062797465206172726179
(1 row)

Evidently there's some relevant detail you're not telling us.

            regards, tom lane

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> Carel Combrink <carel.combrink@gmail.com> writes:
>> I am using a PostgreSQL 9.1 server and the following query is perfectly
>> valid for a BYTEA type column:
>> INSERT INTO files VALUES(18,'\x536f6d6520746573742062797465206172726179');

>> But for a server running version 9.3 the above query fails with the
>> following error:
>> ERROR:  array value must start with "{" or dimension information
>> LINE 1: insert into files values(18,'\x504b030414000600080000002100e...

TL> Works for me:

Here, too when the column is a bytea.  But in the quoted web thread, it
says that he writes that the column is not a bytea, but rather a bytea[].

So the question it seems he really wants to know is whether inserting a
non array value to an array column changed from inserting the value to
the first entry in the array to generating a syntax error.

Of course, why that column is an array I cannot guess.  Perhaps it
wasn't on his older server?

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6

James Cloos <cloos@jhcloos.com> writes:
> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> TL> Carel Combrink <carel.combrink@gmail.com> writes:
>>> I am using a PostgreSQL 9.1 server and the following query is perfectly
>>> valid for a BYTEA type column:
>>> INSERT INTO files VALUES(18,'\x536f6d6520746573742062797465206172726179');

>>> But for a server running version 9.3 the above query fails with the
>>> following error:
>>> ERROR:  array value must start with "{" or dimension information
>>> LINE 1: insert into files values(18,'\x504b030414000600080000002100e...

> TL> Works for me:

> Here, too when the column is a bytea.  But in the quoted web thread, it
> says that he writes that the column is not a bytea, but rather a bytea[].

> So the question it seems he really wants to know is whether inserting a
> non array value to an array column changed from inserting the value to
> the first entry in the array to generating a syntax error.

Well, I get that same error from both 9.1 and current if I declare the
column as bytea[].  I think there's some other discrepancy we've not
been told about.

            regards, tom lane

James Cloos wrote
>>>>>> "TL" == Tom Lane <

> tgl@.pa

> > writes:
>
> TL> Carel Combrink <

> carel.combrink@

> > writes:
>>> I am using a PostgreSQL 9.1 server and the following query is perfectly
>>> valid for a BYTEA type column:
>>> INSERT INTO files
>>> VALUES(18,'\x536f6d6520746573742062797465206172726179');
>
>>> But for a server running version 9.3 the above query fails with the
>>> following error:
>>> ERROR:  array value must start with "{" or dimension information
>>> LINE 1: insert into files values(18,'\x504b030414000600080000002100e...
>
> TL> Works for me:
>
> Here, too when the column is a bytea.  But in the quoted web thread, it
> says that he writes that the column is not a bytea, but rather a bytea[].
>
> So the question it seems he really wants to know is whether inserting a
> non array value to an array column changed from inserting the value to
> the first entry in the array to generating a syntax error.
>
> Of course, why that column is an array I cannot guess.  Perhaps it
> wasn't on his older server?

In psql:

SELECT array[ decode('these are the bytes','escape'), decode('and so are
these','escape') ]::bytea[]::text AS bat
\gset

SELECT :'bat';
>> {"\\x...","\\x..."}

SELECT encode( ( (:'bat')::byte[] )[1],'escape');
>> these are the bytes

As this is a type conversion error you should be able to log the complete
raw SQL statement and compare it to the content that is generated when
working with psql directly.  The above provides an example of working code
using 9.3.

The OP issue, as posted here, is that the insert is trying to put a scalar
value into a column that is expecting an array.  This is a schema mis-match
that has nothing to do with PostgreSQL but is strictly the responsibility of
the application.

I would suggest testing you code with something like short binary encoded
text like I did above and not megabyte large, truly binary, files.

One possible confounding issue is mixing "escape" and "hex" encoding schemes
for bytea textual representation.  These were mostly resolved in the 8
series but depending on upgrade patterns and library upgrades these may
still be impacting you.

Short answer is to inspect what the server is seeing when you do use the
library to perform your actions and if the library is generating
non-conforming SQL fix the library to match the syntax PostgreSQL is
expecting AND/OR provide us with a fully functional example of the syntax
you are seeing and get our opinions on it.  A self-contained, select-only,
query is best but if you need to create schema objects please supply their
definitions.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BYTEA-PostgreSQL-9-1-vs-9-3-tp5789671p5793418.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.