From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | psycopg(at)postgresql(dot)org |
Subject: | Re: psycopg3 and adaptation choices |
Date: | 2020-11-10 03:11:48 |
Message-ID: | CA+mi_8b5hKEp72V34ngSL0yaYNLJOsZ+PH9hKx9tYkwH9_FPhQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
On Tue, 10 Nov 2020 at 01:19, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2020-11-10 03:20:11 | Re: psycopg3 and adaptation choices |
Previous Message | Adrian Klaver | 2020-11-10 01:19:30 | Re: psycopg3 and adaptation choices |