Containment query on range types fails with DataError

[stj]

• asyncpg version: 0.28.0
• PostgreSQL version: 11.15
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Locally against postgres docker
• Python version: 3.11
• Platform: Linux
• 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?: Didn't try uvloop

Bug description

Trying to run a containment query against a range type. https://www.postgresql.org/docs/13/rangetypes.html

Simplified version of the SQL

SELECT tstzrange('[2023-10-01 00:00Z, 2024-01-01 00:00Z)') @> now()

Using asyncpg with a python datetime

>>> import asyncpg
>>> from datetime import datetime, timezone
>>> timestamp = datetime.now(tz=timezone.utc)
>>> con = await asyncpg.connect()
>>> await con.execute("SELECT tstzrange('[2023-10-01 00:00Z, 2024-01-01 00:00Z)') @> $1", timestamp)
...
File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute
  File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $1: datetime.datetime(2023, 10, 27, 20, 24, ... (list, tuple or Range object expected (got type <class 'datetime.datetime'>))

Same behaviour is observed with other range types

SELECT int4range(10, 20) @> 3;

File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg asyncpg.exceptions.DataError: invalid input for query argument $1: 3 (list, tuple or Range object expected (got type <class 'int'>))

Expected behaviour

Allow the argument of a containment query to be of the range type (int, datetime, date).

[elprans]

PostgreSQL cannot infer the correct type of the argument from a query like this, you should cast it explicitly like this:

SELECT tstzrange('[2023-10-01 00:00Z, 2024-01-01 00:00Z)') @> $1::timestamptz

[stj]

Amazing! Thanks for the quick response.