Go postgres `SELECT * IN` using array

Question

I have a simple select statement:

Select * FROM X where X.name in ("bob", "joe") and X.phone='123'

That works fine in postgres,

In my Go code I have the following code:

var phone string = "123"
var names []string = []string{"bob", "joe"}
sqlStatement := `Select * FROM X where X.name in ($1) and X.phone=$2`
rows, sqlerr := db.Query(sqlStatement, names, phone)

but for some reason I error out from that sql.

unsupported Scan, storing driver.Value type into type *string

how can i use my names array in side the sqlstatement?

note: if i do a fmt.printf and paste the sql statement into postgres, i do get data back + if i take out the $1, and manually input the strings

Use X.name = ANY ($1) with lib/pq array types.

mkopriva
– mkopriva

2018-11-06 19:16:56 +00:00
Commented Nov 6, 2018 at 19:16 — mkopriva
– mkopriva, Commented Nov 6, 2018 at 19:16
Possible duplicate of Go and IN clause in Postgres

Jonathan Hall
– Jonathan Hall

2018-11-06 19:46:37 +00:00
Commented Nov 6, 2018 at 19:46 — Jonathan Hall
– Jonathan Hall, Commented Nov 6, 2018 at 19:46

peterSO · Accepted Answer · 2018-11-06 19:24:02Z

6

Copying and pasting fragments from some working Go PostgreSQL code:

import (
    "database/sql"
    "github.com/lib/pq"
)

    query := `
            . . .
            WHERE code = ANY($1)
           . . .

        `

    codes := []string{"JFK", "LGA", "EWR"}
    rows, err := db.Query(query, pq.Array(codes))

answered Nov 6, 2018 at 19:24

peterSO

168k32 gold badges302 silver badges293 bronze badges

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

Comments

Nate · Accepted Answer · 2018-11-08 15:08:58Z

0

I solved this using http://jmoiron.github.io/sqlx/#inQueries

var phone string = "123"
var names []string = []string{"bob", "joe"}
sqlStatement := `Select * FROM X where X.name in (?) and X.phone=?`
sqlStatement, args, err := sqlx.In(sqlStatement, names, phone)
sqlStatement = db.Rebind(sqlStatement)
        rows, sqlerr := db.Queryx(sqlStatement, args...)

this now returns correctly.

another way to solve this was to use fmt.Sprintf() and converting the ? to %s

answered Nov 8, 2018 at 15:08

Nate

1,7904 gold badges25 silver badges42 bronze badges

Collectives™ on Stack Overflow

Go postgres `SELECT * IN` using array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related