Re: psycopg3 and adaptation choices

On Mon, 9 Nov 2020 at 06:57, Federico Di Gregorio <fog(at)dndg(dot)it> wrote:

> In your example I'd just go for int8 (the largest possible int in
> PostgreSQL). Decimal would probably be better (largest range) but it is
> not what the majority of people would expect.

The problem with int8 is that it fails in all the cases where decimal
fails, while not allowing to pass values larger than 64 bits:

piro=# select '[10,20,30]'::jsonb -> 1::int8;
ERROR: operator does not exist: jsonb -> bigint
LINE 1: select '[10,20,30]'::jsonb -> 1::int8;
^
HINT: No operator matches the given name and argument types. You
might need to add explicit type casts.

You are right though that, in terms of round-trippig, decimal might be
unexpected too:

>>> cur.execute("select %s", [10]).fetchone()[0]
Decimal('10')

So uhm... that's a +1 for that option too :D

> IMHO, oid is a bad idea
> because it has a very specific semantic and the error messages generated
> by PostgreSQL will be more confusing.

I'm not sure I understand this. At the moment, the oids are something
that don't really surface to the end-users, who are not required to
use them explicitly and shouldn't be seen in the error messages. For
instance the query above might results in a call:

>>> from psycopg3.oids import builtins
>>> builtins["numeric"].oid
1700

>>> res = conn.pgconn.exec_params(b"select '[]'::jsonb -> $1",
[b"1"], [1700])
>>> res.status
<ExecStatus.FATAL_ERROR: 7>

>>> print(res.error_message.decode("utf8"))
ERROR: operator does not exist: jsonb -> numeric
LINE 1: select '[]'::jsonb -> $1
^
HINT: No operator matches the given name and argument types. You
might need to add explicit type casts.

So the oid is only used internally, in the mapping python type ->
exec_params() types array, the 1700 shouldn't surface anywhere.

Maybe I'm misunderstanding your concern: can you tell me better?

-- Daniele