MIME-Version: 1.0
References: 
 <CA+mi_8Zdk_beGcbOjkpdo+fchTR5ot1XM2zZc=ujSGqP+WVh-g@mail.gmail.com>
 <2b9859f0-0964-2baa-b6bc-13f975ae0f67@aklaver.com>
 <CA+mi_8ZfV+rEDKaqM-pcSf43FuN4FUJGoGTxvEEXap-x_GWP0Q@mail.gmail.com>
 <88fb7a7e-a182-a816-c1a7-8a1f54b65215@aklaver.com>
 <CA+mi_8YUEPfTPqD-1Jxm6NUwgJpWxPgejRZ+2-mPQr=0DQWiHA@mail.gmail.com>
 <5d2207c6-cebb-082d-cf5b-a7a0fe8e58d4@aklaver.com>
In-Reply-To: <5d2207c6-cebb-082d-cf5b-a7a0fe8e58d4@aklaver.com>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Tue, 10 Nov 2020 03:11:48 +0000
Message-ID: 
 <CA+mi_8b5hKEp72V34ngSL0yaYNLJOsZ+PH9hKx9tYkwH9_FPhQ@mail.gmail.com>
Subject: Re: psycopg3 and adaptation choices
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: psycopg@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

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