Sqlx: When using "pgx" driver, sqlx interprets Postgres integer[] array as string and fails to Scan()

[ARolek]

When using the pgx driver the following query where ids is a Postgres array integer[] fails with the error: sql: Scan error on column index 0: unsupported driver -> Scan pair: string -> *[]int32

conn, err = sqlx.Connect("pgx", ...)
if err != nil {
    log.Fatal(err)
}

query := fmt.Sprintf(`
    SELECT 
        ids
    FROM 
        %v
    WHERE
        id=1
`, IdsTable)

var ids []int32
err = conn.QueryRow(query).Scan(&ids)

If I use the pgx driver directly, the array is decoded into a Go slice fine. Here's the code:

conn, err := pgx.Connect(config)
if err != nil {
    log.Fatal(err)
}

query := fmt.Sprintf(`
    SELECT 
        ids
    FROM 
        %v
    WHERE
        id=1
`, IdsTable)

var ids []int32
err = conn.QueryRow(query).Scan(&ids)

Any idea why using sqlx is interpreting the Postgres array integer[] as a string?

[jmoiron]

The default type is []byte since that is what comes off the wire. The database/sql interface allows you to create custom scan destinations, but drivers can also add additional types that they support (eg lib/pq supports time.Time for datetime types). The pgx interface must support this things through its own interface but not through the database/sql driver mode. Can you verify that this fails using regular database/sql with pgx?

[jmoiron]

Closing this as it's been open for a long time without any updates and it looks like it's a discrepancy in the driver.

[jeromer]

I reproduced the problem. Complete code below :

package main

import (
    "fmt"

    "github.com/jackc/pgx"
    _ "github.com/jackc/pgx/stdlib"
    "github.com/jmoiron/sqlx"
)

type User struct {
    Field []int32
}

const SQL = "SELECT field FROM testint32"

func main() {
    db := connectPgx()
    dropTable(db)
    createTable(db)

    fmt.Println("TEST PGX ...")
    testPgx(db)

    fmt.Println("TEST SQLX ...")
    testSqlx()
}

func dropTable(db *pgx.Conn) {
    _, err := db.Exec("DROP TABLE IF EXISTS testint32")
    if err != nil {
        panic(err)
    }
}

func createTable(db *pgx.Conn) {
    _, err := db.Exec("CREATE TABLE testint32( field integer[] NOT NULL DEFAULT ARRAY[]::integer[])")
    if err != nil {
        panic(err)
    }

    _, err = db.Exec("INSERT INTO testint32 VALUES(ARRAY[1, 2]::integer[])")
    if err != nil {
        panic(err)
    }
}

func testPgx(db *pgx.Conn) {
    var x []int32
    db.QueryRow(SQL).Scan(&x)
    fmt.Printf("X: %+v\n\n", x)
}

func connectPgx() *pgx.Conn {
    conn, err := pgx.Connect(pgx.ConnConfig{
        Host:              "localhost",
        Port:              5432,
        Database:          "mydb",
        User:              "postgres",
        Password:          "",
        TLSConfig:         nil,
        UseFallbackTLS:    false,
        FallbackTLSConfig: nil,
        Logger:            nil,
        RuntimeParams: map[string]string{
            "client_encoding": "UTF8",
            "timezone":        "UTC",
        },
    })

    if err != nil {
        panic(err)
    }

    return conn
}

func testSqlx() {
    db := sqlx.MustConnect("pgx", "postgres://postgres:@localhost:5432/mydb?sslmode=disable")

    var u User
    err := db.Get(&u, SQL)
    if err != nil {
        panic(err)
    }
    fmt.Printf("%+v\n", u)
}

Output:

➜  test  go run main.go 
TEST PGX ...
X: [1 2]

TEST SQLX ...
panic: sql: Scan error on column index 0: unsupported Scan, storing driver.Value type string into type *[]int32

goroutine 1 [running]:
main.testSqlx()
    /tmp/test/main.go:84 +0x1b5
main.main()
    /tmp/test/main.go:26 +0x146
exit status 2

[kdebacker]

I would like to ask if there was ever a solution to this problem since I am now running into the exact same error.

[jkchen46034]

drop sqlx. It is not complete.