Examples or documentation for "WHERE field IN $1" - should be "WHERE field IN ($1)"

[wittekm]

Is your feature request related to a problem? Please describe. I ran into a hard-to-debug situation with a query like the following:

roleAssignmentUuids := []string{"a-a-a", "b-b-b"}
queryStr := `
SELECT stuff FROM table
WHERE role_assignment_id IN $1;` 
// this is wrong, it only works when you use ($1)
rows, err := client.Query(ctx, queryStr, roleAssignmentUuids)

Describe the solution you'd like Provide an example in examples/ that demonstrates the correct placeholder usage in this case.

Describe alternatives you've considered An alternative would be to prevent against this case in code, but, eh.

[jackc]

🤷‍♂️

It's not really a pgx issue. It's a bad query. You'd get the exact same results in psql.

For example:

postgres@[local]:5015 pgx_test=# create temporary table t (id int primary key);
CREATE TABLE
Time: 3.058 ms
postgres@[local]:5015 pgx_test=# prepare s as select * from t where id in $1;
ERROR:  42601: syntax error at or near "$1"
LINE 1: prepare s as select * from t where id in $1;
                                                 ^
LOCATION:  scanner_yyerror, scan.l:1192
Time: 0.948 ms

And you don't want to use IN ($1) either. You want to use PostgreSQL any.

e.g.

postgres@[local]:5015 pgx_test=# prepare s as select * from t where id = any($1);
PREPARE
Time: 1.350 ms
postgres@[local]:5015 pgx_test=# execute s('{1,2,3}');
 id
────
(0 rows)

Time: 7.613 ms

See https://www.postgresql.org/docs/current/functions-comparisons.html#FUNCTIONS-COMPARISONS-ANY-SOME