public inbox for [email protected]
help / color / mirror / Atom feedFrom: Daniele Varrazzo <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: psycopg3 and adaptation choices
Date: Tue, 10 Nov 2020 03:11:48 +0000
Message-ID: <CA+mi_8b5hKEp72V34ngSL0yaYNLJOsZ+PH9hKx9tYkwH9_FPhQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+mi_8Zdk_beGcbOjkpdo+fchTR5ot1XM2zZc=ujSGqP+WVh-g@mail.gmail.com>
<[email protected]>
<CA+mi_8ZfV+rEDKaqM-pcSf43FuN4FUJGoGTxvEEXap-x_GWP0Q@mail.gmail.com>
<[email protected]>
<CA+mi_8YUEPfTPqD-1Jxm6NUwgJpWxPgejRZ+2-mPQr=0DQWiHA@mail.gmail.com>
<[email protected]>
On Tue, 10 Nov 2020 at 01:19, Adrian Klaver <[email protected]> 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
view thread (23+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: psycopg3 and adaptation choices
In-Reply-To: <CA+mi_8b5hKEp72V34ngSL0yaYNLJOsZ+PH9hKx9tYkwH9_FPhQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox