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

**URL:** <https://forum.crystal-lang.org/t/db-postgres-utf8-exception-with-binary-bytea-column-type/5623>\
**Category:** Help & Support\
**Created:** [May 2, 2023, 5:40am UTC](https://forum.crystal-lang.org/t/db-postgres-utf8-exception-with-binary-bytea-column-type/5623 "2023-05-02T05:40:33Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![compumike](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/compumike/32/1722_2.png) [@compumike](https://forum.crystal-lang.org/u/compumike)\
**Post date:** [May 2, 2023, 5:40am UTC](https://forum.crystal-lang.org/t/db-postgres-utf8-exception-with-binary-bytea-column-type/5623/1 "2023-05-02T05:40:33Z")

</div>

I just posted an [issue on crystal-pg](https://github.com/will/crystal-pg/issues/267) 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](https://www.postgresql.org/docs/current/datatype-binary.html). 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.)

---

<div class="post-metadata">

**Author:** ![npn](https://avatars.discourse-cdn.com/v4/letter/n/e68b1a/32.png) [@npn](https://forum.crystal-lang.org/u/npn)\
**Post date:** [May 2, 2023, 10:33am UTC](https://forum.crystal-lang.org/t/db-postgres-utf8-exception-with-binary-bytea-column-type/5623/2 "2023-05-02T10:33:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![compumike](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/compumike/32/1722_2.png) [@compumike](https://forum.crystal-lang.org/u/compumike)\
**Post date:** [May 2, 2023, 3:26pm UTC](https://forum.crystal-lang.org/t/db-postgres-utf8-exception-with-binary-bytea-column-type/5623/3 "2023-05-02T15:26:52Z")

</div>

FYI I found a workaround by pre-encoding my `Array(Bytes?)` into an `Array(String?)` in [postgres bytea hex format](https://www.postgresql.org/docs/current/datatype-binary.html#id-1.5.7.12.9).

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

```crystal
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.

---

<div class="post-metadata">

**Author:** ![npn](https://avatars.discourse-cdn.com/v4/letter/n/e68b1a/32.png) [@npn](https://forum.crystal-lang.org/u/npn)\
**Post date:** [May 2, 2023, 8:59pm UTC](https://forum.crystal-lang.org/t/db-postgres-utf8-exception-with-binary-bytea-column-type/5623/4 "2023-05-02T20:59:06Z")

</div>

> 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.