IN Lookup Postgres query in prepared statement, Golang

Question

The use case requires running of exclusion queries. Something like:

select col1 
from awesome_table 
where col2 not in (a,b,c,d) 
and col3 not in (a1,a2,a3,a4);

As the set of excluded col1 values and excluded col2 values is variable sized, what is a good way to generate the prepared statement? One hack that I can think of is to define an upper limit on the set say 15 and fill all placeholders with repeated values if number of query set size input by user is less than max value, is there a better way? And how are prepared statements suppose to handle this, as per the philosophy of the community?

Related / possible duplicate: [Go and IN clause in Postgres](https://stackoverflow.com/questions/38036752/go-and-in-clause-in-postgres/38037586#38037586) — icza, Feb 03 '20 at 13:05

score 1 · Answer 1 · answered Feb 03 '20 at 13:09

Can you pass (Postgres) arrays from Go?

Then you could rewrite the statement to

where col2 <> ALL ($1) 
  and col3 <> all ($2)

where $1 and $2 are (Postgres) arrays containing the values.

If you can't pass proper array instances, you can pass the values as a string that's formatted so that it can be cast to an array.

select col1 
from awesome_table 
where col2 <> ALL ( (cast $1 as int[]) ) 
  and col3 <> ALL ( (cast $2 as text[]) );

Then you could pass '{1,2,3}' for the first parameter and e.g. '{"foo", "bar"}' as the second parameter. You need to adjust the array types to the actual data types of your columns

What characteristics will make a go structure qualify as a Postgres array? — dbot_5, Feb 04 '20 at 12:59
@dbot_5: sorry, no idea. I have no experience with Go and its driver interaction with Postgres. But the second solution will always work and doesn't impose a performance penalty (compared to the first) — , Feb 04 '20 at 13:01

score 0 · Accepted Answer · answered May 15 '20 at 07:00

Adding to @a_horse_with_no_name's answer, In Golang, the psql driver github.com/lib/pq contains a method Array() that can be used to convert a Golang slice into a psql Array.

...

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

...

select col1 
from awesome_table 
where col2 <> ALL ($1) 
  and col3 <> ALL ($2);

where

slice1 := []string{val1, val2}
slice2 := []string{val3, val4}

pq.Array(slice1) can be passed for $1 and pq.Array(slice2) can be passed for $2 placeholder while passing the values in the prepared statements.

More about ANY and ALL functions can be found at here

IN Lookup Postgres query in prepared statement, Golang

2 Answers2