1

I'm trying to write a stream of bytes into my postgres server (PostgreSQL 9.5beta2, Windows 8 64bit).

The insert query is as follows:

PreparedStatement insert = Database.prepare("INSERT INTO hierarchical_correlation (reference, target, values) VALUES (?,?,?::BYTEA) ON CONFLICT (reference, target) DO UPDATE SET values = EXCLUDED.values")

The schmea for hierarhical_correlation is

CREATE TABLE public.hierarchical_correlation (
  reference HANDLE NOT NULL,
  target HANDLE NOT NULL,
  values BYTEA,
  PRIMARY KEY (reference, target)
);

And I'm inserting the values lik this:

final byte[] score = new byte[]{127,-128,127}; // These are exemplary values, but I have checked and my values are always in that range.
final String reference = scapeplot.reference.hash;
final String target = scapeplot.resolve(0).get().hash;

insert.setString(1, reference);
insert.setString(2, target);

insert.setBinaryStream(3, new ByteArrayInputStream(score));
insert.setBytes(3, score); // I have tried both

Since byte in Java is of range -128 to 127, and as far as I can tell postgres has 0-255 I expect -128 to be 0 and 127 to be 128 to 255. When I evaluate the above, and check its value I get something like(those are obviously not the equivalent bytes, but the values the below string generated where either 128 or -127) 

'7F807F80807F8080808080808080808080'

This is obviously kinda right, but completely wrong: 0x7F is 127, which is right, given no rebasing by JDBC and 0x80 is 128, which obviously is wrong, but given that it produces and overflow, we are at the right value.

So how would I go about inserting these values? I've tried changing, neither value produces the results I want. 

bytea_output = 'escape'         # hex, escape

I actually suspect it's a bug on the JDBC side, but I've not yet exhausted my positivity.

awildturtok
  • 173
  • 1
  • 13
  • This works just fine for me. How do you know the wrong values are stored? Did you try `setBytes()` as an alternative? –  Feb 13 '16 at 17:59
  • I've managed to find the culprit: 1) The values were stored correctly, but in a weird fashion which is fine by me. 2) The way I retrieved the data was as an Array of BYTEA which has no builtin array type. Then I had to figure out how to use the PGArray class and used the resultset, which contains for one row two elements of which the first does not seem to contain relevant data(for me) and the second is the payload I was interested in. – awildturtok Feb 13 '16 at 19:24
  • An "array of bytea" doesn't make sense. And you don't need any special PGArray class to retrieve this. Just use `getBytes()` or `getBinaryStream()` on the `ResultSet` –  Feb 13 '16 at 20:48
  • Please read up on how [two's-complements](https://en.wikipedia.org/wiki/Two's_complement) works. 0x80 **is** -128 in two's complement for a signed byte. – Mark Rotteveel Feb 14 '16 at 08:43
  • Note that my vote to close is purely based on your statement _"I expect -128 to be 0 and 127 to be 128 to 255."_ which to be me indicates that your entire problem revolves around two's complements. If it isn't, I suggest you update your question to explain in more detail what you expected and why. – Mark Rotteveel Feb 14 '16 at 08:51
  • An array of bytea makes sense if you join your data in psql, Instead of querying 60 times I do it once and get an array of bytea each representing different relations. Yeah, my confusion was on the two complement side. My bad actually. – awildturtok Feb 14 '16 at 10:22
  • `SELECT reference, array_agg(target::TEXT), array_agg(values) FROM hierarchical_correlation GROUP BY reference` This is the query in question, (more or less). Thank you mark! – awildturtok Feb 14 '16 at 10:35

0 Answers0