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.

[MKrkkl]

Old issue, but if someone gets here please use array types from https://pkg.go.dev/github.com/lib/pq (if you wanna keep using sqlx)

import ( "github.com/lib/pq" ) type Foo struct { Bar pq.Int32Array }