Why is the slice not passed to the pg array?

Question

I'm trying to pass a slice to a query that has a cte, but I get an error

Goland code:

insertQuery = `
   WITH cte AS (SELECT $1                AS id,
                    UNNEST(ARRAY($2)) AS address)
   INSERT
   INTO user_addresses
   SELECT id, address
   FROM cte;
`
addresses = []string{"firstAddress", "secondAddress", "thirdAddress"}
_, err = db.ExecContext(ctx, insertQuery, id, addresses)
if err != nil {
    log.Println(err)
}

I get an error:

ERROR: at or near \")\": syntax error (SQLSTATE 42601)

I tried to transfer the slice directly to pg.Array, but the result is the same. What is the reason for the error?

Answer 1

I think the problem here is your query, how you are using UNNEST(ARRAY($2)), here $2 refers entire address slice, in your case it taking the entire address array instead is individual element.

Answer 2

As @Tejanidhi pointed out, you are using UNNEST(ARRAY($2)), here $2 refers the address of slice. To solve this issue github.com/lib/pq already offers StringArray(), Int32Array(), BoolArray() and more methods. Here is example usage:

addresses = []string{"firstAddress", "secondAddress", "thirdAddress"}
_, err = db.ExecContext(ctx, insertQuery, id, pq.StringArray(addresses))
if err != nil {
    log.Println(err)
}