[1.22.0] Parameter value type has changed.

[gomachssm]

Hello,

The execution result changed between 1.21 and 1.22.

>>> cur.execute('select :p1 ;', {'p1': 1})
>>> cur.fetchall()

At v1.21, ([1],) .
At v1.22, (['1'],) .
The parameter type is int , but the return type is str .

There is another one.

>>> cur.execute('select :p1 + make_interval(hours => 1) ;', {'p1': datetime.datetime(2021, 1, 2, 3, 4)})
>>> cur.fetchall()

At v1.21, ([datetime.datetime(2021, 1, 2, 4, 4)],) .
At v1.22, raise pg8000.dbapi.ProgrammingError in cur.execute().
Error log

pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '22007', 'M': 'invalid input syntax for type interval: "2021-01-02T03:04:00"', 'F': 'datetime.c', 'L': '3755', 'R': 'DateTimeParseError'}

..\..\..\venv\ct02\lib\site-packages\pg8000\legacy.py:277: ProgrammingError

Am I having problem using it?

[tlocke]

Hi @gomachssm, the reason for this is that pg8000 now lets PostgreSQL determine the parameter types from the query, rather than pg8000 choosing them from the parameter type. So for your query:

select :p1 ;

PostgreSQL assumes a parameter type of text, but if you want the parameter to by of type integer you can do:

select cast(:p1 as integer);

similarly with your query:

select :p1 + make_interval(hours => 1) ;

PostgreSQL is guessing that the parameter is of type interval, but if you want to make it of type timestamp you can do:

select cast(:p1 as timestamp) + make_interval(hours => 1) ;

[gomachssm]

Hi @tlocke , thank you for answer.

I see, use cast in SQL.
However, what I want is that the parameter types are used as-is in SQL, as in v1.21.
In v1.22, if we want to use a type other than text, we always need a cast, so I don't want that.

Why was this fix necessary?
Is it possible to modify the behavior like v1.21? X-(
I don't want to write a lot of casts in SQL ....

[tlocke]

Sorry this version is working well for you @gomachssm, for a lot of people they're doing queries on a table, so something like:

import pg8000.dbapi

con = pg8000.dbapi.connect("postgres")
cur = con.cursor()
cur.execute("CREATE TEMPORARY TABLE book (id INTEGER)")
cur.execute("INSERT INTO book (id) VALUES (%s)", (1,))
cur.execute("SELECT id FROM book")
print(cur.fetchall())

which gives the output ([1],), with no CAST needed. In the examples you give there is no existing table, and so PostgreSQL can't work out the type of the parameter. There's an alternative method for your case and that's to use the setinputsizes() method of the cursor:

import pg8000.dbapi

con = pg8000.dbapi.connect("postgres")
cur = con.cursor()
cur.setinputsizes(pg8000.dbapi.INTEGER)
cur.execute("select %s", (1,))
print(cur.fetchall())

which gives the output ([1],). I'm interested in understanding your use-case for queries where there isn't an existing table?

[gomachssm]

Hi @tlocke , thank you for your reply!

I see.
In my case, the return value was wrong because there is no "from table" in the query.
It works fine if "from table" is included in the query.

I found that there was a problem with my usage.
So, this issue is closed. :D