CODEWITHSUNDEEP
sqlpostgresqlgo
cigrainger
cigrainger

Reputation: 2226

How do I get a slice from a Postgres array in Golang?

Let's say I have a postgres query like:

SELECT
  id,
ARRAY_AGG(code) AS code
  FROM
    codes
WHERE id = '9252781' 
GROUP BY id;

My return looks like:

 id        |  codes
-----------+-------------
 9252781   | {H01,H02}

Both id and codes are varchar.

In Golang when I scan along the rows for my result, it just freezes. No error, nothing.

Upvotes: 2

Views: 4928

Answers (2)

ton
ton

Reputation: 4607

I don't want to use a additional driver (github.com/lib/pq), and I did not found any pgx way to do it other than creating my own type.

So I did one:

type Tags []string
func (t *Tags) Scan(v interface{}) error {
        if v == nil {
                *t = Tags{}
                return nil
        }
        s, ok := v.(string)
        if !ok {
                return fmt.Errorf("Scan is expected to receive a string from database, but got [%+v]", v)
        }
        s = strings.TrimPrefix(s, "{")
        s = strings.TrimSuffix(s, "}")
        *t = strings.Split(s, ",")
        return nil
}
func (t *Tags) Value() (driver.Value, error) {
        s := fmt.Sprintf("{%v}", strings.Join(([]string)(*t), ","))
        return s, nil
}

then you can scan the row returned from database like:

...
    err := rows.Scan(
        ...
        &Tags,
        ...
    )

and you can use it directly you your Exec queries.

This code works well with constants in arrays, but you need more work if want to use it in commas and brackets.

Upvotes: 1

mkopriva
mkopriva

Reputation: 38343

If you're using the github.com/lib/pq postgres driver you can use their pq.Array helper function to scan and store postgres arrays.

var id string
var arr []string

row := db.QueryRow(`SELECT '9252781', ARRAY['H01','H02']`)
if err := row.Scan(&id, pq.Array(&arr)); err != nil {
    log.Fatal(err)
}

log.Println(id, arr)
// 9252781 [H01 H02]

Upvotes: 5

Related Questions