英文:
Multi-column IN/ANY postgres query
问题
示例表模式：
create table t1(col1 varchar(20),col2 varchar(20));
要求：
获取与数组中提供的任何元组（col1，col2）匹配的行。
SQL：
select * from t1 where (col1, col2) in (('c11', 'c12'), ('c21', 'c22'));
我想使用“database/sql”包和“github.com/lib/pq”驱动程序在Go中编写此查询，这就是我遇到问题的地方。
对于单列IN/ANY查询，我可以很容易地实现。
例如，以下查询
select * from t1 where col1 in ('c11', 'c21');
可以使用以下代码片段实现：
args := []string{"c11", "c21"}conn.Exec(`select * from t1 where col1 = any($1)`, pq.Array(args))
然而，我无法对多列查询使用类似的方法。
我尝试将pq.Array([][]string)，pq.Array([]*pq.StringArray)等作为参数传递，但它们不起作用，并返回以下错误：
input of anonymous composite types is not implemented
对此将不胜感激。
英文:
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.
答案1
得分: 1
你可以这样做：
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...)
对于一个两列的元组，你可以这样做：
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"}}))
英文:
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"}}))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论