public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniele Varrazzo <[email protected]>
To: Federico Di Gregorio <[email protected]>
Cc: [email protected]
Subject: Re: psycopg3 and adaptation choices
Date: Mon, 9 Nov 2020 12:00:55 +0000
Message-ID: <CA+mi_8a+Q0C2LwiedwNkRPVY=-G3q0zW6w19zYvvADtdEd4b_w@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 Mon, 9 Nov 2020 at 06:57, Federico Di Gregorio <[email protected]> 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

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], [email protected]
  Subject: Re: psycopg3 and adaptation choices
  In-Reply-To: <CA+mi_8a+Q0C2LwiedwNkRPVY=-G3q0zW6w19zYvvADtdEd4b_w@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