CODEWITHSUNDEEP
postgresqlgo
Sankar
Sankar

Reputation: 6522

IN pq.Array fails to pass values

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

Upvotes: 0

Views: 1249

Answers (1)

mkopriva
mkopriva

Reputation: 38203

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.

Upvotes: 3

Related Questions