in_ doesn't produce valid postgres syntax

[jasongi]

Using in_ with a list/tuple won't work. You end up with an error like this:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$11"

The solution could be to generate the tuple from the iterable ourselves.

There's a related asyncpg issue here:

https://github.com/MagicStack/asyncpg/issues/767

and also in their FAQ

https://magicstack.github.io/asyncpg/current/faq.html#why-do-i-get-postgressyntaxerror-when-using-expression-in-1

[samuelcolvin]

Can you show the query, ideally simplified.

[jasongi]

@samuelcolvin sure, the sample from the readme has the same issue:

from buildpg import V, funcs, render

where_logic = V('foo.bar') == 123
if spam_value:
   where_logic &= V('foo.spam') <= spam_value

if exclude_cake:
   where_logic &= funcs.not_(V('foo.cake').in_([1, 2, 3]))

render('select * from foo where :where', where=where_logic)

Note: I've added the where in the query on the last line above, which isn't the in readme, but without it the you'll get a different syntax error.

[samuelcolvin]

This code does not run and does not explain the question you're asking. It would be really helpful when asking questions like this if you provided a minimal, reproducible, example.

Without knowing what you're asking, I think what you probably need is:

from buildpg import V, funcs
where = V('foobar') == funcs.any([1, 2, 3])

[jasongi]

@samuelcolvin that example is directly pulled from the readme and produces invalid postgres syntax.

You can see from the output that it renders to foo.cake in $3 which is explained in the asyncpg docs as invalid postgres syntax.

Why do I get PostgresSyntaxError when using expression IN $1?

expression IN $1 is not a valid PostgreSQL syntax. To check a value against a sequence use expression = any($1::mytype[]), where mytype is the array element type.

I can produce a failing test case, but will take me some time to check out and setup the repo.

Perhaps it would make more sense to remove in_ from the readme and the library and just document using any instead? Alternatively this is how psycopg deals with the IN issue, which I think would mean a separate parameter for each item.

[samuelcolvin]

yes, I agree we should remove in_ from the readme and use any or something else.

PR welcome to update that.