Обсуждение: BYTEA: PostgreSQL 9.1 vs 9.3
Hi,
Was there a change in the PostgreSQL server for how BYTEA fields are defined in a SQL Query to the server?
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');
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
ERROR: array value must start with "{" or dimension information
LINE 1: insert into files values(18,'\x504b030414000600080000002100e...
Is this something that changed on the server from 9.1 to 9.3 or is it based on a setting somewhere that could affect this?
This problem was picked up during a conversation on a Qt Forum that can be seen here. I am trying to find out where the solution should be to make the Qt code and the PostgreSQL server compatible again for a proper bug report (if needed) at the correct place.
Regards,
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.