Read a Postgresql array directly into a Golang Slice

Question

I have a query which returns a row with a single column containing an array of strings (character varying[]):

{http://wp.me/p62MJv-Jc,http://tyrant.click/1LGBoD6}

Is there any easy way to read this directly into a Golang slice? E.g.

var arr []string

for rows.Next() {
    rows.Scan(&arr)
    fmt.Println(len(arr))
}

Produces:

0

Answer 1

I think this should do the job. Using array_to_json in sql. Then unmarshalling the json-string to golang slice

sql-> select array_to_json(arr) from ....

var arrStr string
var arr []string

for rows.Next() {
    rows.Scan(&arrStr)
    json.Unmarshal([]byte(arrStr), &arr)
    fmt.Println(len(arr))
}

Answer 2

As mentioned by Victor in the comments on the original post, this post answers the question well with its explanation of pq.Array().

Taken directly from the link:

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
}

I've just got this working in a project of my own as well, so can confirm it works.

Answer 3

At the moment, there is no direct way to load a PostgreSQL array into a Go slice using the lib/pq library. It may be at some point, but there has been some debate over whether such a thing should be handled transparently by the library itself.

However, one option is to load the result into a string (that looks like {item1,item2,"comma,item"}), and then split that string into a string slice using a regular expression, as done in the code below (taken in part from this Gist by Andrew Harris):

import (
    "regexp"
    "strings"
)

var (
    // unquoted array values must not contain: (" , \ { } whitespace NULL)
    // and must be at least one char
    unquotedChar  = `[^",\\{}\s(NULL)]`
    unquotedValue = fmt.Sprintf("(%s)+", unquotedChar)

    // quoted array values are surrounded by double quotes, can be any
    // character except " or \, which must be backslash escaped:
    quotedChar  = `[^"\\]|\\"|\\\\`
    quotedValue = fmt.Sprintf("\"(%s)*\"", quotedChar)

    // an array value may be either quoted or unquoted:
    arrayValue = fmt.Sprintf("(?P<value>(%s|%s))", unquotedValue, quotedValue)

    // Array values are separated with a comma IF there is more than one value:
    arrayExp = regexp.MustCompile(fmt.Sprintf("((%s)(,)?)", arrayValue))
)

// Parse the output string from the array type.
// Regex used: (((?P<value>(([^",\\{}\s(NULL)])+|"([^"\\]|\\"|\\\\)*")))(,)?)
func pgArrayToSlice(array string) []string {
    var valueIndex int
    results := make([]string, 0)
    matches := arrayExp.FindAllStringSubmatch(array, -1)
    for _, match := range matches {
        s := match[valueIndex]
        // the string _might_ be wrapped in quotes, so trim them:
        s = strings.Trim(s, "\"")
        results = append(results, s)
    }
    return results
}

Here is how it might be used:

rows, err := db.Query("SELECT link FROM links")
if err != nil {
    panic(err)
}
var tmp string
for rows.Next() {
    rows.Scan(&tmp)
    links := pgArrayToSlice(tmp)
    fmt.Println(len(links), links)
}

With the following in the database:

# \d links
    Table "public.links"
 Column |  Type  | Modifiers 
--------+--------+-----------
 link   | text[] | 

# select * from links;
             link             
------------------------------
 {this,that}
 {another,thing}

 {}
 {"test,123","one,two,three"}
(5 rows)

This is what is output by the Go code above:

2 []string{"this,", "that"}
2 []string{"another,", "thing"}
2 []string{"another,", "thing"}
0 []string{}
2 []string{"test,123\",", "one,two,three"}