Bool arrays incompatible with github.com/lib/pq

if i have a table like
create table foo (
id int primary key,
value bool[]
)
and I do “select value from foo”, the wire representation coming back from cockroach for the actual bool values in the array are in the form of “true” and “false”. lib/pq only looks for ‘t’ and ‘f’ exclusively when parsing using the pq.Array wrapper. Is there a way around this other than changing my table’s data type to something like int[] or changing lib/pq? I have a pull request at https://github.com/lib/pq/pull/690 but who knows when or if ever that will get merged.

Hi,

Thanks for bringing this up. We do use t/f for regular bool values, but apparently not for bool arrays. This is probably something we should fix. I am requesting some more input from someone who is more familiar with arrays and lib/pq.

Hi @nuggien,

Thanks for letting us know! I will work on getting a fix for that ASAP. In the meantime as a workaround, you might check out pgx which I tested and does handle these arrays.

Justin

@justin @radu thanks for the quick responses! I’ll check out pgx.

i’m using pgx to insert bool arrays, and i’m getting the following error: “ERROR: error in argument for $1: unsupported OID 1000 with format code formatBinary (SQLSTATE XX000)”

val := new(pgtype.BoolArray)
val.Set([]bool{true, false})
err := conn.Exec("insert into foo values(1, $1)", val)
if err != nil {
    log.Println(err)
}

am I missing something or is this usecase not supported? Any help would be appreciated!

@justin does @nuggien’s approach look any different from the one you took?

Please note that doing a SELECT into a pgtype.BoolArray works just fine. It’s just the INSERT that doesn’t work.

@nuggien the error message that you provided above made me realize that the discrepancy is due to the format that pgx is sending the bool array in. For context, Postgres drivers can send datum values in either a text or a binary format. We do our best to support both in all cases, but this is a situation where it looks like we’re missing a decoding routine for the binary format.

I opened https://github.com/cockroachdb/cockroach/issues/21433 to track this.