List[str] parameter treated as text instead of text[] in certain requests

[EricKnowsCodeFu]

• asyncpg version: 0.27.0
• PostgreSQL version: 15.1
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: No
• Python version: 3.8.10
• Platform: Ubuntu
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and
  uvloop?: Yes

I'm working with a custom bulk operations API built on top of SQLAlchemy and asyncpg, and some scenarios the List parameters are not treated as postgres arrays.

Example 1:

import asyncio
import asyncpg

async def try_it(table: str, query: str, *params, **connargs):
   conn = await asyncpg.connect(**connargs)
   try:
     await conn.execute(f'CREATE TABLE test_table({table});')
     await conn.execute(query, *params)
   finally:
     await conn.execute('DROP TABLE test_table;')
     await conn.close()

table = 'a text[], b text'
query = """
UPDATE test_table SET a = uvals.a
FROM (VALUES ($1, $2)) AS uvals (a, b)
WHERE test_table.b = uvals.b
"""
params = [ ['hello', 'world'], 'helloworld']

db_conn_params = {}
asyncio.get_event_loop().run_until_complete(try_it(table, query, *params, **db_conn_params))

Response:

asyncpg.exceptions.DatatypeMismatchError: column "a" is of type text[] but expression is of type text

Example 2:

# same imports and try_it() from above

table = 'a text, b int'
query = """
SELECT a, b
FROM test_table
UNION
  SELECT
    values as a,
    5 as b
  FROM unnest($1) as values
"""
params = [ ['hello', 'world'] ]

# execution as above

Response:

asyncpg.exceptions.AmbiguousFunctionError: function unnest(unknown) is not unique

Adding a $1 :: text[] solves the problem in each case, since it appears to be passing the list as text but there are scenarios where I don't have direct control of the SQL (it being auto-generated).

[elprans]

Alas, I'm not sure what can be done here. asyncpg relies on Postgres telling it the expected input types (rather than directly deriving the types from the types of arguments). Unfortunately, PostgreSQL's inference is imperfect.

[EricKnowsCodeFu]

Appreciate the insight. I was able to tweak the bulk operations API with manual type hints so that the generated SQL contains the postgres cast statements. Not necessarily ideal but solves the immediate problem for my use-case.