Golang Postgresql Array

Question

If I have a table that returns something like:

id: 1
names: {Jim, Bob, Sam}

names is a varchar array.

How do I scan that back into a []string in Go? I'm using lib/pg

Right now I have something like

rows, err := models.Db.Query("SELECT pKey, names FROM foo")
for rows.Next() {
            var pKey int
            var names []string
            err = rows.Scan(&pKey, &names)
}

I keep getting:

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

It looks like I need to use StringArray https://godoc.org/github.com/lib/pq#StringArray

But, I think I'm too new to Go to understand exactly how to use: func (a *StringArray) Scan(src interface{})

mkopriva · Accepted Answer · 2017-11-24 21:32:56Z

13

You are right, you can use StringArray but you don't need to call the

func (a *StringArray) Scan(src interface{})

method yourself, this will be called automatically by rows.Scan when you pass it anything that implements the Scanner interface.

So what you need to do is to convert your []string to *StringArray and pass that to rows.Scan, like so:

rows, err := models.Db.Query("SELECT pKey, names FROM foo")
for rows.Next() {
            var pKey int
            var names []string
            err = rows.Scan(&pKey, (*pq.StringArray)(&names))
}

answered Nov 24, 2017 at 21:32

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

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

3 Comments

Chemdream Over a year ago

This worked! So, What exactly is this doing? Is it simply (function)(param)?

mkopriva Over a year ago

It's a type conversion, Type(value); sometimes, as in this case, you need parentheses around the type e.g. (*Type)(value). More info and examples here: golang.org/ref/spec#Conversions

Jonas Over a year ago

pq.StringArray can not handle null, but PostgreSQL arrays can contain NULL. A better approach is to use pq.Array() as in ak89224's answer, in combination with sql.NullString.

ak89224 · Accepted Answer · 2022-04-07 17:55:38Z

Long Story Short, use like this to convert pgSQL array to GO array, here 5th column is coming as a array :

var _temp3  []string
            for rows.Next() {
                // ScanRows scan a row into temp_tbl
                err := rows.Scan(&_temp, &_temp0, &_temp1, &_temp2, pq.Array(&_temp3))

In detail :

To insert a row that contains an array value, use the pq.Array function like this:

 // "ins" is the SQL insert statement
ins := "INSERT INTO posts (title, tags) VALUES ($1, $2)"

// "tags" is the list of tags, as a string slice
tags := []string{"go", "goroutines", "queues"}

// the pq.Array function is the secret sauce
_, err = db.Exec(ins, "Job Queues in Go", pq.Array(tags))

To read a Postgres array value into a Go slice, use:

func getTags(db *sql.DB, title string) (tags []string) {
    // the select query, returning 1 column of array type
    sel := "SELECT tags FROM posts WHERE title=$1"

    // wrap the output parameter in pq.Array for receiving into it
    if err := db.QueryRow(sel, title).Scan(pq.Array(&tags)); err != nil {
        log.Fatal(err)
    }

    return
}

Note: that in lib/pq, only slices of certain Go types may be passed to pq.Array().

Another example in which varchar array in pgSQL in generated at runtime in 5th column, like :

   --> predefined_allow false admin iam.create {secrets,configMap}

I converted this as,

Q := "SELECT ar.policy_name, ar.allow, ar.role_name, pro.operation_name, ARRAY_AGG(pro.resource_id) as resources FROM iam.authorization_rules ar LEFT JOIN iam.policy_rules_by_operation pro ON pro.id = ar.operation_id GROUP BY ar.policy_name, ar.allow, ar.role_name, pro.operation_name;"
        tx := g.db.Raw(Q)
        rows, _ := tx.Rows()
        defer rows.Close()
        var _temp   string
        var _temp0   bool
        var _temp1  string
        var _temp2  string
        var _temp3  []string
        for rows.Next() {
            // ScanRows scan a row into temp_tbl
            err := rows.Scan(&_temp, &_temp0, &_temp1, &_temp2, pq.Array(&_temp3))
            if err != nil {
                return nil, err
            }
            fmt.Println("Query Executed...........\n", _temp, _temp0, _temp1, _temp2, _temp3)
        }

Output :

Query Executed...........
predefined_allow false admin iam.create [secrets configMap]

Collectives™ on Stack Overflow

Golang Postgresql Array

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related