DB / postgres: UTF8 exception with binary bytea[] column type

Help & Support
1

I just posted an issue on crystal-pg but I’m unsure if the problem is actually in that shard or might be in the main crystal-db shard or elsewhere… does anyone know the right place to investigate?

The quick summary is that I’m getting an Unhandled exception: invalid byte sequence for encoding "UTF8": 0x00 (PQ::PQError) while I’m sending non-ASCII binary data such as a simple Bytes[0, 255] in Postgres’s binary bytea type. I don’t see why it should be getting turned into UTF8 to go over the wire.

(My actual use case is a bulk upsert query on a column of arbitrary binary data, but I trimmed down the example for filing this issue.)

2

I have the same problem. Haven’t found the solution yet.

Also anyone know how to read citext[] column. Should be pretty trivial but somehow it escapes me.

3

FYI I found a workaround by pre-encoding my Array(Bytes?) into an Array(String?) in postgres bytea hex format.

See this comment on Exception sending query with bytea[] binary array type? · Issue #267 · will/crystal-pg · GitHub for the full code snippet with workaround, but the basic outline is:

String.build do |str|
  str << "\\x"
  bytes.each do |byte|
    str << sprintf("%02x", byte)
  end
end

@npn I’m not sure about your citext[] issue. That may be a result set decoding issue, rather than a bind parameter encoding problem like I think I hit.

4

That may be a result set decoding issue

yep it’s about decoding issue, I just post it here in case, sorry.

also have you succeeded rewrite it by extending PQ::Params struct instead? the solution above works but it is just converting it to string beforehand instead, which is a hack as best. at that point you can just convert it to hexbytes string then using decode($str, 'hex') in the sql statement too. which in my opinion equally inelegant.