Unexpected/undesirable `CAST` of date string to `VARCHAR`

• asyncpg version: 0.29.0
• PostgreSQL version: 15.7
• Python version: 3.11
• Platform: macOS 13.6
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: no (poetry)

start_date = '2024-01-01'
query.where(MyTable.datetime_col >= start_date)

This will fail:

<class 'asyncpg.exceptions.UndefinedFunctionError'>: operator does not exist: timestamp without time zone >= character varying

It seems that this is being casted as VARCHAR:

SELECT * FROM my_table WHERE datetime_col >= $1::VARCHAR

This same filter is valid in Postgres

SELECT * FROM my_table WHERE datetime_col >= '2024-01-01'

It works when using the datetime object:

start_date = dt.datetime.strptime(start_date, "%Y-%m-%d")
query.where(MyTable.datetime_col >= start_date)

Wonder if this is somewhat similar to #1169 in the sense that casting/argument handling invalidates valid SQL statements.

I'd have imagined that castings were performed in obvious and non-breaking scenarios, and scenarios where casting would be necessary, but are not obvious should be handled directly by the user. Breaking valid SQL statements seems counter intuitive IMHO.

Riproduci il caso query.where usando asyncpg 0.29.0, PostgreSQL 15.7 e Python 3.11, confrontando gli input string e datetime. Esamina l’SQL generato e la gestione dei parametri intorno al cast $1::VARCHAR segnalato. Il lavoro è completato quando il comportamento previsto del confronto delle date è stato stabilito ed è coperto da un test di regressione, oppure è stato dimostrato che il problema appartiene a un’altra parte.