how to pass an array as a one parameter?

[AoAnima]

how to pass an array as a one parameter? select * from table where value in ($1) $1=['val1', 'val2'.....]

[jackc]

Arrays are handles the same as any other type of parameter as far as the PostgreSQL server and the pgconn library are concerned. But pgconn is probably lower level than you want.

I would suggest you might want to go up a layer and use https://github.com/jackc/pgx instead. With pgx you can do something like:

ary := []string{"foo", "bar", "baz"}
... := conn.Query(ctx, "select * from table where value in ($1)", ary)

[zuckermanori]

@jackc the example above doesn't work. it returns an error "cannot convert [foo bar baz] to Text". I'm using pgx v4. has anything changed? is there any way to make this work?

[jackc]

@zuckermanori Nothing has changed, but it looks look I made a mistake with the SQL. It should be select * from table where value = any ($1) instead.

[zuckermanori]

Thanks @jackc I changed from IN to ANY and it now works as expected.

[its-felix]

I cant get this to work, however I want to do it.

I'm using pgx v5.5.0 with cockroachdb/cockroach:v23.1.8 trying to query on a TEXT[] column.

I have tried the following:

1. passing the []string as a single parameter: where value = any($1) with params []any{[]string{"gw2:account", "gw2:builds"}}

2. passing the []string in "exploded" form: where value = any(ARRAY[$1, $2]) with params []any{"gw2:account", "gw2:builds"}

in both cases I get the following error: could not parse "gw2:account" as type string[]: array must be enclosed in { and } (SQLSTATE 22P02)

Replacing ARRAY[ %s ] with { %s } doesnt work either, the error in that case is ERROR: at or near "{": syntax error (SQLSTATE 42601)

What is weird that the same code works without any:

// both prefix and suffix are empty strings in my testcases
builder.AddSlice(values, func(nums []int) string {
	return fmt.Sprintf("%s%s = ARRAY[ %s ]%s", prefix, prop, strings.Join(util.SQLParams(nums), ","), suffix)
})

builder in this case is just a simple helper:

package util

import "strconv"

type SQLBuilder struct {
	offset int
	expr   []string
	params []any
}

func NewSQLBuilder(offset int) *SQLBuilder {
	return &SQLBuilder{
		offset: offset,
		expr:   make([]string, 0),
		params: make([]any, 0),
	}
}

func (b *SQLBuilder) Add(v any, expr func(int) string) {
	b.expr = append(b.expr, expr(b.offset+len(b.params)))
	b.params = append(b.params, v)
}

func (b *SQLBuilder) AddSlice(v []any, expr func([]int) string) {
	l := len(b.params)
	nums := make([]int, len(v))
	for i := 0; i < len(nums); i++ {
		nums[i] = b.offset + l + i
	}

	b.expr = append(b.expr, expr(nums))
	b.params = append(b.params, v...)
}

func (b *SQLBuilder) Get() ([]string, []any) {
	return b.expr, b.params
}

func SQLParam(num int) string {
	return "$" + strconv.Itoa(num)
}

func SQLParams(nums []int) []string {
	values := make([]string, len(nums))
	for i := 0; i < len(nums); i++ {
		values[i] = SQLParam(nums[i])
	}

	return values
}

I'm a bit lost what to try out now, especially since it works without any.

The full code can be viewed here: https://github.com/gw2auth/gw2auth.com-api/commit/5cc4e9899fa6897cf9f9b585c300efa2df277220 most importantly the func translateQuery

[its-felix]

Sorry for the confusion.

I'm migrating from a Java project and it turns out anything in the chain I'm using on the Java side adds support for ANY queries like Postgres on Cockroach. When I tried to run my queries directly against Cockroach I ran into the exact same errors I got from pgx, so it turned out to be a problem on my side.

CockroachDB has a containment operator &&. Using this operator, everything works as expected. My updated SQL for Cockroach looks like this: where value && $1 binding the []string as $1