Pgx: Strange output from Scan() to bytes array

[rkintzi]

Take a look at code at a bottom. If you run us it prints:

$ go run pgx.go
[1 123 34 97 34 58 32 34 98 34 125]
{"a": "b"}
invalid character '\x01' looking for beginning of value
exit status 1

Is there any reason for this first byte in an array? On HEAD~2 (db13650) it is not inserted.

import (
    "encoding/json"
    "fmt"
    "os"

    "github.com/jackc/pgx"
)

func main() {
    cfg, err := pgx.ParseDSN(os.Getenv("TEST_DSN"))
    if err != nil {
        fmt.Fprintf(os.Stderr, "%s\n", err)
        os.Exit(1)
    }
    con, err := pgx.Connect(cfg)
    if err != nil {
        fmt.Fprintf(os.Stderr, "%s\n", err)
        os.Exit(1)
    }
    var bs []byte
    err = con.QueryRow(`select jsonb_build_object('a','b')`).Scan(&bs)
    if err != nil {
        fmt.Fprintf(os.Stderr, "%s\n", err)
        os.Exit(1)
    }
    fmt.Println(bs)
    fmt.Println(string(bs))
    var o map[string]string
    err = json.Unmarshal(bs, &o)
    if err != nil {
        fmt.Fprintf(os.Stderr, "%s\n", err)
        os.Exit(1)
    }
}

[rkintzi]

This additional byte is not inserted for simple query like: select '1'

[jackc]

pgx uses the binary format whenever possible. Scanning into a []byte reads the raw bytes from PostgreSQL. The raw binary format of jsonb is 1 followed by the json text. The 1 is the jsonb format version number.

There are several solutions.

First, scan into string instead of []byte.

Next, cast to json in PostgreSQL.

Lastly, given that you are unmarshalling the json data into another object, it is probably best to Scan directly into the object. pgx can automatically unmarshal json and jsonb into anything the json.Unmarshal can. e.g. You could scan directly into a map[string]string.

[rkintzi]

I just want to send bytes retrieved from db to a client. There is no json.Unmarshal() in my http.Handler. Is there any real use case when this additional byte is useful? What else you can do with it besides bs = bs[1:].

What if I want to write tool like pgAdmin3 and really don't know what query I have executed. How can I tell if I have to remove that byte or not?

On the other hand, If I know that I have to remove that byte (I know that column is of type jsonb) I can add it as well if I need it.

[jackc]

You can disable binary encoding for jsonb:

pgx.DefaultTypeFormats["jsonb"] = pgx.TextFormatCode

This will force the text format (which does not have the initial 1 byte) for jsonb universally (this can also be done at the prepared statement level).

As far as the general case of a pgAdmin3 type tool, you might want to look into the Values method.

[jackc]

In the just released v3 of pgx this is no longer an issue out of the box. You can read into a pgtype.JSONB type and access the raw bytes _sans_ version prefix byte in the Bytes field.