Multi-column IN/ANY postgres query-CodePudding

Sample table schema:

create table t1(
  col1 varchar(20),
  col2 varchar(20)
);

Requirement: Fetch rows that match any of the tuples (col1, col2) presented in an array.

SQL:

select * from t1 where (col1, col2) in (('c11', 'c12'), ('c21', 'c22'));

I want to write this query in Go using the "database/sql" package with "github.com/lib/pq" driver, and that is where I am facing the problem.

I can easily do this for single column IN/ANY query.

for example, the following query

select * from t1 where col1 in ('c11', 'c21');

can be achieved with the following code snippet:

args := []string{"c11", "c21}
conn.Exec(`select * from t1 where col1 = any($1)`, pq.Array(args))

However, I could not use similar approach for multi-column query. I tried passing pq.Array([][]string), pq.Array([]*pq.StringArray) etc. as argument, but they do not work, and fetches the following error:

input of anonymous composite types is not implemented

Will appreciate any help on this.

CodePudding user response：

You could do the following:

args := [][]string{{"c11","c21"},{"c21","c22"}}
params := make([]interface{}, len(args)*2)
tuples := make([]string, len(args))
for i := range args {
    params[i*2] = args[i][0]
    params[i*2 1] = args[i][1]
    tuples[i] = fmt.Sprintf("($%d,$%d)", i*2 1,i*2 2)
}
invals := "("   strings.Join(tuples, ","))   ")"
conn.Exec("SELECT * FROM t1 WHERE (col1,col2) IN "   invals, params...)

For a two-column tuple you should be able to do the following:

conn.Exec(`SELECT * FROM t1 WHERE (col1,col2) IN (
    SELECT * FROM json_each_text(json_object($1::text[]))
)`, pq.Array([][]string{{"c11","c21"},{"c21","c22"}}))