PostgreSQL, pgx, sqlc and bytea

Subtitle: if you know what these four words mean, then this post is for you

This is a quick one  as I’m not really in a blogging mood. But I couldn’t for the life of me find a way to decode a PostgreSQL bytea value (which is what PostgreSQL uses for BLOB values) using pgx and sqlc so that it would match what I actually stored.

The documentation of pgx and sqlc, along with various web searches, yielded nothing. I did eventually find an answer, which I’m posting here for anyone else who happens to run into this problem. That way, you can save the 2 hours it took for me to solve this problem (or I guess you could ask ChatGPT once it’s trained on this post).

I’m guessing that if you were using pgx directly, you’ve got a bit more available to you, such as ByteaCodec or LargeObjects. But we’re using sqlc, and a bytea column is simply mapped to a Go []byte field.

Using this []byte field to get stuff into a bytea column works fine, but if you were to try and read the original bytes back from a query, you’d find that the value looks a bit like a Base64 encoded string:

"bXkgYnl0ZSB2YWx1ZSBhcyBpdCBhcHBlYXJzIGZyb20gYSBzZWxlY3Q="

And it may have been how the debugger displays watch values, but when I was trying to decode this, I was getting decoding errors saying that character 0 was not a value Base64 character. It turns out the reason for this is that double quotes are actually part of the returned value.

So, for anyone else trying to use bytea values with pgx and sqlc, the way to get the original bytes back is to:

  1. Take the []byte value returned from the query
  2. Check the length. If it’s zero, it’s likely to indicate that the value is NULL.
  3. Remove the double quotes, either by passing it through strconv.Unquote or simply taking a subslice
  4. Decode the result as a Base64 value.

This should work regardless of how PostgreSQL encodes bytea values (I tried it with both hex and escape and the procedure seems to be the same).

Go PostgreSQL