Using postgres IN clause in golang

Question

I have been trying to use the postgres IN clause in golang, but keep getting errors. This is the query I want to execute.

SELECT id1 FROM my_table WHERE type = (an int) AND id2 = (an int) AND id1 IN (list of UUIDs)

I used this code to construct this query but got the following error.

var params []interface{}
inCondition := ""
params = append(params, type)
params = append(params, id2)
for _, id := range id1 {
    params = append(params, id)
    if inCondition != "" {
        inCondition += ", "
    }
    inCondition += "?"
}
query := fmt.Sprintf(`SELECT id1 FROM my_table WHERE type = ? AND id2 = ? AND id1 IN (%s)`, inCondition)
rows, err := db.Query(query, params...)

Query I got:

SELECT id1 FROM my_table WHERE type = ? AND id2 = ? AND id1 IN (?, ?, ?)

Params output:

[]interface {}=[0 7545449 d323f8d5-ab97-46a3-a34e-95ceac2f3a6a d323f8d5-ab97-46a3-a34e-95ceac2f3a6b d323f8d5-ab97-46a3-a34e-95ceac2f3a6d]

Error:

pq: syntax error at or near \"AND\""

What am I missing? or, how will I get this to work? id1 is a slice of UUIDs whose length is variable.

never use string interpolation for building queries. At least not without carefully quoting everything. This is massive SQL-injection bait. Use bind parameters instead. e.g. godoc.org/github.com/lib/pq#Array — Craig Ringer
– Craig Ringer, Commented Oct 12, 2017 at 3:23
Are you using lib/pq? For the parameter's place holder, instead of ?, try $1, $2.... — putu
– putu, Commented Oct 12, 2017 at 3:53
Could you show whole result query? May be on database error log. Try to use special interpolation for IN queries. — Eugene Lisitsky
– Eugene Lisitsky, Commented Oct 12, 2017 at 4:30

Rahul Rumalla · Accepted Answer · 2018-03-24 11:29:30Z

3

Ran into a similar issue. Can't remember where exactly I picked this up but I remember still running into issues when dealing with arrays of type integer vs string. What I had to do was to have a local custom type and return a driver compatible value for it. See sample below.

// Int64Array is a type implementing the sql/driver/value interface
// This is due to the native driver not supporting arrays...
type Int64Array []int64

// Value returns the driver compatible value
func (a Int64Array) Value() (driver.Value, error) {
    var strs []string
    for _, i := range a {
        strs = append(strs, strconv.FormatInt(i, 10))
    }
    return "{" + strings.Join(strs, ",") + "}", nil
}

Highly recommend checking out sqlx. Wrote a simple orm wrapper called papergres to make my go + postgres life easier :) Give it a try.

answered Mar 24, 2018 at 11:29

Rahul Rumalla

3332 silver badges9 bronze badges

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

Comments

Ram · Accepted Answer · 2017-10-12 04:27:27Z

0

Instead of ?, using $1,$2 etc as placeholder worked.

answered Oct 12, 2017 at 4:27

Ram

6321 gold badge9 silver badges23 bronze badges

1 Comment

Kaedys Over a year ago

Ya, Postgres doesn't accept the ? placeholder. However, if you use sqlx, it allows you to use ? in any language (as well as named paramaters, like :entry_id), then remap them to the language in question via Rebind(). Has a bunch of other features that make SQL queries a lot less annoying as well (like the ability to scan directly into a structure).

Collectives™ on Stack Overflow

Using postgres IN clause in golang

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related