passing a list\set as an argument failing

[CodyDeGhetto]

• asyncpg version:19.0
• PostgreSQL version:12
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?:N\A
• Python version:3.7.5
• Platform:Windows
• Do you use pgbouncer?:No
• Did you install asyncpg with pip?:Yes
• If you built asyncpg locally, which version of Cython did you use?:N\A
• Can the issue be reproduced under both asyncio and
  uvloop?:

Similar to issue #94

I am trying to set up a query using ($1) and passing an argument. I am running into an issue when trying to pass a list of sets. Is there a way to format this query like the last example?

query = [(0,0), (1,1), (2,2)]

works:    await conn.fetch('SELECT * FROM table WHERE (a, b) IN ((0,0), (1,1), (2,2))')

works:    await conn.fetch('SELECT * FROM table WHERE (a, b) IN ' + str(tuple(query)))

fails:    await conn.fetch('SELECT * FROM table WHERE (a, b) IN ($1)', '((0,0), (1,1), (2,2))')

fails:    await conn.fetch('SELECT * FROM table WHERE (a, b) IN ($1)', str(tuple(query)))

works:    await conn.fetch('INSERT INTO table (a, b, c, d) VALUES ($1, $2, $3, $4)', a, b, c, d)

Thank you

[CodyDeGhetto]

I just read up on https://magicstack.github.io/asyncpg/current/faq.html:

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 still cannot figure out how to format this expression

[eirnym]

@CodyDeGhetto There was a few similar issues like #185 on this, (searched by keyword tuple):

select * from table where (a, b) = any(ARRAY[(0, 0), (1, 1), (2, 2)])

and for any values (my guess):

await conn.fetch('''select * from table where (a, b) = any($1::int[]);''', [(0, 0), (1, 1), (2, 2)])

I hope this helps

[CodyDeGhetto]

@eirnym With that first line I get a specific error:

asyncpg.exceptions.DatatypeMismatchError: cannot compare dissimilar column types smallint and integer at record column 1

I am using smallints for columns a and b.

[eirnym]

While you don't mix column types, it's easy to resolve with the same idea, so it'd be enough just to replace int to smallint in the specific example.

I don't know a good solution for mixed param types, though.

[CodyDeGhetto]

The query data is not coming from a 3rd party, so I feel just appending the query to the end of the sql request is fine in this case, so I will stick with that for now.

I would still like to know to format this request correctly in case I run into this again. Thanks for the input.