具有十六进制值的Golang + Postgres WHERE子句

Question

I created a simple sql database with a BYTEA field,

create table testhex (testhex bytea);
insert into testhex (testhex) values ('\x123456');

and then I tried to query it from Go.

package main

    import (
        "database/sql"
        _ "github.com/lib/pq"
    )

    func main(){
        var err error

        db, err := sql.Open("postgres", "dbname=testhex sslmode=disable")
        if err != nil {
            panic(err)
        }

        var result string
        err = db.QueryRow("select testhex from testhex where testhex = $1", `\x123456`).Scan(&result)
        if err != nil {
            panic(err)
        }
    }

It doesn't find the row. What am I doing wrong?

Answer 1

When you ran the following query:

insert into testhex (testhex) values ('\x123456');

You inserted the 3 byte sequence [0x12 0x34 0x56] into the table. For the database query you're executing with QueryRow though, you're searching for the 8 character literal string \x123456 so you get no matches in the result.

When you use positional arguments with QueryRow, it is the database adapter's job to convert them to a form the database understands (either by sending them to the database as bound parameters, or by substituting them into the query with appropriate escaping). So by passing an already escaped value you will run into this sort of problem.

Instead, try passing []byte{0x12, 0x34, 0x56} as the positional argument, which should match what is in the database.