Query for an integer array from PostreSQL always returns []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
[]

You're using `[]int`, but the error says `*[]int64`. Are you sure your code matches your error message? — Jonathan Hall, Dec 24 '17 at 17:34

Jonathan Hall · Accepted Answer · 2017-12-24T17:57:30.017

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)

Mohd Rashid · Answer 2 · 2022-12-04T06:53:18.087

The problem will be more severe if you use fetching multiple rows. The above code works for a single row, to fetch multiple rows use like this

`rows, err := db.QueryContext(ctx, stmt, courseCode) if err != nil { return nil, err } defer rows.Close()

var feedbacks []*Feedback1

for rows.Next() {
    var feedback Feedback1
    var ret pq.Int64Array
    var ret1 pq.Int64Array
    err := rows.Scan(
        &feedback.ID,
        &ret,
        &ret1,
    )
    if err != nil {
        return nil, err
    }

    //for loop to convert int64 to int
    for i:=0;i<len(ret);i++{
        feedback.UnitFeedback = append(feedback.UnitFeedback,int(ret[i]))}

    for i:=0;i<len(ret1);i++{
        feedback.GeneralFeedback = append(feedback.GeneralFeedback,int(ret1[i]))}

    feedbacks = append(feedbacks, &feedback)
}`

Query for an integer array from PostreSQL always returns []uint8

2 Answers2