从PostgreSQL查询整数数组总是返回[] uint8

Question

Take a simple PostreSQL db with an integer array:

CREATE TABLE foo (
    id                       serial PRIMARY KEY,
    bar               integer[]
);

INSERT INTO foo VALUES(DEFAULT, '{1234567, 20, 30, 40}');

Using pq, these values are for some reason being retrieved as an array of []uint8.
The documentation says that integer types are returned as int64. Does this not apply to arrays as well?

db, err := sql.Open("postgres", "user=a_user password=your_pwd dbname=blah")
if err != nil {
    fmt.Println(err)
}

var ret []int
err = db.QueryRow("SELECT bar FROM foo WHERE id=$1", 1).Scan(&ret)
if err != nil {
    fmt.Println(err)
}

fmt.Println(ret)

Output:

sql: Scan error on column index 0: unsupported Scan, storing driver.Value type []uint8 into type *[]int64
[]

Answer 1

You cannot use a slice of int as a driver.Value. The arguments to Scan must be of one of the supported types, or implement the sql.Scanner interface.

The reason you're seeing []uint8 in the error message is that the raw value returned from the database is a []byte slice, for which []uint8 is a synonym.

To interpret that []byte slice appropriately as a custom PostgreSQL array type, you should use the appropriate array types defined in the pq package, such as the Int64Array.

Try something like this:

var ret pq.Int64Array
err = db.QueryRow("SELECT bar FROM foo WHERE id=$1", 1).Scan(&ret)
if err != nil {
    fmt.Println(err)
}

fmt.Println(ret)