Wrong element type on WHERE Clause

[SkipUFO]

Hello. I'm trying select value from table where value = ANY($1) and parameter pgtype.VarcharArray value has type - character varying with Int4Array - construction with ANY - has no errors

[jackc]

Not sure what may or may not be going on with pgtype.VarcharArray or pgtype.Int4Array, but you probably shouldn't be using them in this query. The main reason to use them as opposed to a normal Go slice is if your PostgreSQL array is multi-dimensional or contains a NULL. But ANY and NULL don't go together. So a normal slice should be sufficient.

Try something like this instead:

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v4"
)

func main() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	var dst bool
	err = conn.QueryRow(context.Background(), `select 'foo' = any($1)`, []string{"foo", "bar", "baz"}).Scan(&dst)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Error: %v\n", err)
		os.Exit(1)
	}
	fmt.Println(dst)
}

[SkipUFO]

Thanks, your example - helps. Is it right that you recommend to use pgtypes only in multidimensional arrays or if array may contain NULL value? In other cases, using slices is better.

[jackc]

Is it right that you recommend to use pgtypes only in multidimensional arrays or if array may contain NULL value? In other cases, using slices is better.

Yes, it is usually simpler to use a slice directly.