英文:
IN pq.Array fails to pass values
问题
我有以下代码：
package main
import (
"database/sql"
"fmt"
"github.com/lib/pq"
)
const (
DB_USER = "<username>"
DB_PASSWORD = "<password>"
DB_NAME = "<db>"
)
func main() {
dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",
DB_USER, DB_PASSWORD, DB_NAME)
db, err := sql.Open("postgres", dbinfo)
checkErr(err)
defer db.Close()
fmt.Println("# 使用 SQL 进行黑名单查询")
rows, err := db.Query(`
SELECT * FROM(
SELECT x from (values ('A'), ('B'), ('C') ) s(x)
) As Res1 WHERE x NOT IN ('A');
`)
checkErr(err)
for rows.Next() {
var str string
err = rows.Scan(&str)
fmt.Println(str) // 输出 B,C
}
fmt.Println("使用 Golang 进行黑名单查询")
blacklist := []string{"A"}
q := `
SELECT * FROM(
SELECT x from (values ('A'), ('B'), ('C') ) s(x)
) As Res1 WHERE x NOT IN ($1);
`
rows, err = db.Query(q, pq.Array(blacklist))
checkErr(err)
for rows.Next() {
var str string
err = rows.Scan(&str)
fmt.Println(str) // 输出 B,C
}
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
在这段代码中，我将 pq.Array 作为参数传递给了 Golang 的 Postgres 格式字符串 $1。然而，参数未能传递。当我期望输出 B,C 时，程序却打印出了 A,B,C。
# 使用 SQL 进行黑名单查询
B
C
使用 Golang 进行黑名单查询
A
B
C
希望对你有帮助！
英文:
I have the following code:
package main
import (
"database/sql"
"fmt"
"github.com/lib/pq"
)
const (
DB_USER = "<username>"
DB_PASSWORD = "<password>"
DB_NAME = "<db>"
)
func main() {
dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",
DB_USER, DB_PASSWORD, DB_NAME)
db, err := sql.Open("postgres", dbinfo)
checkErr(err)
defer db.Close()
fmt.Println("# Querying with blacklist in SQL")
rows, err := db.Query(`
SELECT * FROM(
SELECT x from (values ('A'), ('B'), ('C') ) s(x)
) As Res1 WHERE x NOT IN ('A');
`)
checkErr(err)
for rows.Next() {
var str string
err = rows.Scan(&str)
fmt.Println(str) // Prints B,C
}
fmt.Println("Querying with blacklist in Golang")
blacklist := []string{"A"}
q := `
SELECT * FROM(
SELECT x from (values ('A'), ('B'), ('C') ) s(x)
) As Res1 WHERE x NOT IN ($1);
`
rows, err = db.Query(q, pq.Array(blacklist))
checkErr(err)
for rows.Next() {
var str string
err = rows.Scan(&str)
fmt.Println(str) // Prints A, B, C
}
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
where I pass a pq.Array as a parameter to a Golang Postgres format string $1. However the parameter fails to get passed. When I expect an output of B,C, the program is printing A,B,C.
# Querying with blacklist in SQL
B
C
Querying with blacklist in Golang
A
B
C
答案1
得分: 3
Postgres的IN不接受类似ANY或ALL的数组，而在这些情况下可以使用pq.Array。
你可以使用x <> ALL($1)来替代使用x NOT IN($1)。
来自ALL的文档：
左侧表达式被评估，并使用给定的运算符与数组的每个元素进行比较，该运算符必须产生布尔结果。如果所有比较都返回true（包括数组没有元素的情况），则ALL的结果为“true”。如果找到任何false结果，则结果为“false”。
英文:
Postgres's IN does not take an array like for example ANY or ALL which is where you can use pq.Array.
Instead of using x NOT IN($1) you can use x <> ALL($1).
From ALL's docs.
> The left-hand expression is evaluated and compared to each element of
> the array using the given operator, which must yield a Boolean result.
> The result of ALL is "true" if all comparisons yield true (including
> the case where the array has zero elements). The result is "false" if
> any false result is found.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论