Re: psycopg3 and adaptation choices

On Tue, 10 Nov 2020 at 01:19, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 11/8/20 2:21 PM, Daniele Varrazzo wrote:

> > 1. If we specify `numeric` or `int8` as oid, inserting in an int field
> > in a table will work ok, but some functions/operators won't (e.g. "1
> > > %s").
>
> What is not working here?

Postgres has two different types of cast rules: "assignment" and
"implicit". https://www.postgresql.org/docs/current/sql-createcast.html
Assignment casts are only chosen by the parser on insert. Implicit
casts are chosen in other contexts too. It appears that the cast rules
from numeric/int8 to integer are of the first kind. So, while
inserting into a table with a mismatching type mostly works:

    piro=# create table mytable  (myint integer);
    CREATE TABLE
    piro=# insert into mytable (myint) values (42::decimal);
    INSERT 0 1
    piro=# insert into mytable (myint) values (84::int8);
    INSERT 0 1

using the wrong type in more generic expression may fail:

    piro=# select 1 << 4::int;
    ?column?
    ----------
          16

    piro=# select 1 << 4::int8;
    ERROR:  operator does not exist: integer << bigint
    LINE 1: select 1 << 4::bigint;
                    ^
    HINT:  No operator matches the given name and argument types. You
might need to add explicit type casts.

So a psycopg statement such as `cur.execute("select 1 << %s", [n])`,
which used to work if bound client-side, must be rewritten as "select
1 << %s::integer" for server-side binding, both if we choose int8 or
numeric as Postgres types to adapt a Python int.

-- Daniele