invalid input for query argument (can't subtract offset-naive and offset-aware datetimes)

asyncpg/protocol/prepared_stmt.pyx の204行目と、timestamp 引数のエンコード経路を調査します。まず、提供されている Python/PostgreSQL の再現コードを実行してください。timestamp without time zone に aware datetime を渡しても offset-mismatch error が発生せず、timestamp with time zone のケースでは既存の動作が維持されれば完了です。

• asyncpg version: 0.29.0
• PostgreSQL version: 13.8
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: local postgres 13.8-alpine running in docker
• Python version: 3.12.1
• Platform: macos
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• 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

I'm getting an error:

File "asyncpg/protocol/prepared_stmt.pyx", line 204, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $1: datetime.datetime(2024, 3, 21, 21, 17, 9... (can't subtract offset-naive and offset-aware datetimes)

When passing python's datetime object with timezone details to be stored in a column without timezone. Minimal steps to reproduce:

import asyncio
import datetime

import asyncpg

DSN = 'postgresql://user:password@host:port/database'

CREATE_SQL = """
    DROP TABLE IF EXISTS timestamps_test;
    CREATE TABLE timestamps_test
    (
        ts_no_tz timestamp without time zone,
        ts_tz timestamp with time zone
    );
"""
INSERT_SQL = "INSERT INTO timestamps_test (ts_no_tz, ts_tz) VALUES ($1, $2);"


async def main():
    conn = await asyncpg.connect(DSN)
    try:
        await conn.execute(CREATE_SQL)
        now = datetime.datetime.fromisoformat("2024-03-21 21:17:09.631169+07:00")
        await conn.execute(INSERT_SQL, now, now)
    finally:
        await conn.close()


if __name__ == '__main__':
    asyncio.run(main())

I understand that timezone details would be lost either way, but it's inconsistent with PostgreSQL's native behaviour, when trying to insert the same date with psql:

$ psql 
psql (15.6, server 13.8)
Type "help" for help.

INSERT INTO
    timestamps_test (ts_no_tz, ts_tz)
VALUES
    (TIMESTAMP '2024-03-21 21:17:09.631169+07:00',
    TIMESTAMP WITH TIME ZONE '2024-03-21 21:17:09.631169+07:00');
INSERT 0 1

# SELECT * FROM timestamps_test;
          ts_no_tz          |             ts_tz
----------------------------+-------------------------------
 2024-03-21 21:17:09.631169 | 2024-03-21 14:17:09.631169+00
(1 row)

timezone info is ignored and lost but there's no error.