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

Open
#1,174 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
postgresql, python
Domain
backend, databases

Research direction

Reproduce the query.where case using asyncpg 0.29.0, PostgreSQL 15.7, and Python 3.11, comparing the string and datetime inputs. Inspect the generated SQL and parameter handling around the reported $1::VARCHAR cast. Done means the expected date comparison behavior is established and covered by a regression test or the issue is shown to belong elsewhere.

Written by the indexing model from the issue text.

Description

  • 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.

Dominant language
Python
Stars
8.1k
Forks
469
Avg merge
2d 20h
Merged PRs (30d)
9

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from MagicStack/asyncpg

All issues in MagicStack/asyncpg

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.