DataError and PostgresSyntaxError on queries which work on psycopg2

[negcx]

• asyncpg version: 0.27.0
• PostgreSQL version: 14.7
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: Local
• Python version: 3.10.7, 3.11
• Platform: macOS
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: No (Poetry)
• If you built asyncpg locally, which version of Cython did you use?: n/a
• Can the issue be reproduced under both asyncio and
  uvloop?: n/a

asyncpg throws a DataError when I pass a string and try to cast it as an integer within the query.
asyncpg throws a PostgresSyntaxError when I try to use a parameter with a string and an interval, such as interval $1 where $1 is '2 weeks ago'.

I have created a repository with tests to reproduce the errors here: https://github.com/negcx/asyncpg-type-issue. This also includes passing tests when calling psycopg2. As the code is relatively short, I've also added it here below.

Thank you!

import pytest
import asyncpg
import psycopg2

TABLE = """
CREATE TABLE IF NOT EXISTS asyncpg_issue (
    id SERIAL PRIMARY KEY
    , date DATE
);
"""

DSN = "postgresql://localhost/asyncpg-type-issue"

@pytest.mark.asyncio
async def test_asyncpg_integer_cast():
    """
    asyncpg.exceptions.DataError: invalid input for query argument $1:
    '1' ('str' object cannot be interpreted as an integer)
    """
    conn = await asyncpg.connect(DSN)
    await conn.execute(TABLE)
    await conn.execute(
        "select id, date from asyncpg_issue where id = $1::integer",
        "1"
    )
    await conn.close()

@pytest.mark.asyncio
async def test_asyncpg_interval():
    """
    asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$1"
    """
    conn = await asyncpg.connect(DSN)
    await conn.execute(TABLE)
    await conn.execute(
        "select id, date from asyncpg_issue where date > now() + interval $1",
        '2 weeks ago'
    )
    conn.close()

def test_psycopg_integer_cast():
    conn = psycopg2.connect(dsn=DSN)
    cur = conn.cursor()

    cur.execute(TABLE)
    cur.execute("select id, date from asyncpg_issue where id = (%s)::integer", ("1"))

    cur.close()
    conn.close()

def test_psycopg_interval():
    conn = psycopg2.connect(dsn=DSN)
    cur = conn.cursor()

    cur.execute(TABLE)
    cur.execute(
        "select id, date from asyncpg_issue where date > now() + interval %s",
        ("2 weeks ago",)
    )

    cur.close()
    conn.close()
    

[elprans]

Unlike psycopg2, asyncpg does uses the binary data I/O and never does parameter substitution on the client side. Server-side arguments are used instead, so you should expect certain differences in behavior.

These should work:

@pytest.mark.asyncio
async def test_asyncpg_integer_cast():
    """
    asyncpg.exceptions.DataError: invalid input for query argument $1:
    '1' ('str' object cannot be interpreted as an integer)
    """
    conn = await asyncpg.connect(DSN)
    await conn.execute(TABLE)
    await conn.execute(
        "select id, date from asyncpg_issue where id = $1::integer",
        1  # <- pass an actual Python integer
           # if you really need to pass a string, cast $1 to text first:
           # $1::text::integer.
    )
    await conn.close()

@pytest.mark.asyncio
async def test_asyncpg_interval():
    """
    asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$1"
    """
    conn = await asyncpg.connect(DSN)
    await conn.execute(TABLE)
    await conn.execute(
        "select id, date from asyncpg_issue where date > now() + $1::text::interval",
        '2 weeks ago'
    )
    conn.close()

[negcx]

Thank you - that makes a lot of sense. These do work and I figured there was something like that. I appreciate your help!