postgresql - IN pq.Array 传递值失败

Question

我有以下代码:

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)
    }
}

我将 pq.Array 作为参数传递给 Golang Postgres 格式字符串 $1。但是参数无法传递。当我期望输出 B,C 时，程序正在打印 A,B,C。

# Querying with blacklist in SQL
B
C
Querying with blacklist in Golang
A
B
C

Answer 1

Postgres 的 IN不采用数组，例如 ANY或 ALL您可以在哪里使用 pq.Array .

而不是使用 x NOT IN($1)你可以使用 x <> ALL($1) .

来自 ALL的文档。

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.