Unsupported Scan: SQL UUID Array

Question

I am trying to get an array of UUID from a PostgreSQL DB, this gives the following error:

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

Fetching a single UUID is no problem, but when it is an array, the Scan function infers the type of the elements as uint8.

Is there a workaround / solution for this? Or should I rethink my DB?

Code :

func FetchListIdsForUser(id string, db *sql.DB) ([]string, error) {

        // where the results will be stored

        var (
            lists []string
        )


        // statement prep

        stmt, err := db.Prepare("select lists from users where object_id = $1")
            if err != nil {
            formattedError := er.New("FetchListIdsForUser SQL Select: " + err.Error())
            log.Println(formattedError)
            return nil,formattedError
        }
        defer stmt.Close()


        // query happening

        rows, err := stmt.Query(id)
        if err != nil {
            formattedError := er.New("FetchListIdsForUser SQL Query: " + err.Error())
            log.Println(formattedError)
            return nil,formattedError
        }

        defer rows.Close()


        // for each row

        for rows.Next() {

            // scan : this is where the error happens.

            err := rows.Scan(&lists)
            if err != nil {
                formattedError := er.New("FetchListIdsForUser SQL Scan: " + err.Error())
                log.Println(formattedError)
                return nil,formattedError
            }
            return lists,nil
        }

        err = rows.Err()
        if err != nil {
            formattedError := er.New("FetchListIdsForUser: " + id + " Does Not Exist")
            log.Println(formattedError)
            return nil,formattedError
        }
        return nil,er.New("FetchListIdsForUser: " + id + " Does Not Exist")
}

What driver are you using and what's the structure of your table? — fl0cke
– fl0cke, Commented Mar 18, 2016 at 12:42
@fl0cke I am using database/sql github.com/lib/pq The column in question contains an array of UUID. — R Menke
– R Menke, Commented Mar 18, 2016 at 12:57
@fl0cke At the moment I'm trying a different approach and it seems to work: I Scan and store in a []byte and slice into parts of 16. These I Parse to UUID. — R Menke
– R Menke, Commented Mar 18, 2016 at 13:01
@R Menke So lists is a UUID[] and the query returns one row, is that correct? — fl0cke
– fl0cke, Commented Mar 18, 2016 at 13:14
@fl0cke I query the lists Table for one row that matches a certain variable. The column I fetch contains an array of UUID in every row. — R Menke
– R Menke, Commented Mar 18, 2016 at 13:18

fl0cke · Accepted Answer · 2016-03-18 13:44:55Z

1

Your code can be simplified a lot:

func FetchListIdsForUser(id string, db *sql.DB) ([]string, error) {

        rows, err := db.Query("SELECT unnest(lists) FROM users WHERE object_id = $1", id)
        if err != nil { 
            formattedError := errors.New("FetchListIdsForUser SQL Query: " + err.Error())
            return nil, formattedError
        }

        defer rows.Close()

        var lists []string
        var list string

        for rows.Next() {
            // scan every item of the array and append it to lists
            err := rows.Scan(&list)
            if err != nil {
                formattedError := errors.New("FetchListIdsForUser SQL Scan: " + err.Error())
                return nil,formattedError
            }
            lists = append(lists, list)
        }

        if lists == nil {
            // no rows returned
            formattedError := errors.New("FetchListIdsForUser: " + id + " Does Not Exist")
            return nil, formattedError
        }

        return lists, nil
}

db.Query() will prepare (and cache) the statement for you. There's no need to do that manually.
I added unnest(lists) to the select statement. This will produce one row for every element in the array.
In the for loop, we scan every element individually and append it to lists.

Also, this driver supports scanning arrays out of the box.

answered Mar 18, 2016 at 13:44

fl0cke

2,9041 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R Menke Over a year ago

Can you maybe add how to use that package as a driver to your answer? I tried and could not find example code and got completely stuck :( Thank you!

fl0cke Over a year ago

@R Menke You need to import _ "github.com/jackc/pgx/stdlib" and then use "pgx" as the driver name. See the comments in this file github.com/jackc/pgx/blob/master/stdlib/sql.go

Collectives™ on Stack Overflow

Unsupported Scan: SQL UUID Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related