From: Tom Lane <tgl@sss.pgh.pa.us>
To: Darafei "Komяpa" Praliaskouski <me@komzpa.net>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Cast jsonb to numeric, int, float, bool
Date: Mon, 12 Mar 2018 12:43:20 -0400
Message-ID: <8775.1520873000@sss.pgh.pa.us> (raw)
In-Reply-To: <CAC8Q8t++bOLPHqb1ui5Gg+w0vuhEoAg7B__1XpgT8xzi4MF2sA@mail.gmail.com>
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
<9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru>
<151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org>
<76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru>
<CAC8Q8t+LDe9XMohGVnhNE3GgFhsVMJ38JvEVNf1DG1tg=mGD5Q@mail.gmail.com>
<af9cb89f-390e-9dce-74cf-f50371116847@postgrespro.ru>
<29252.1520866819@sss.pgh.pa.us>
<CAC8Q8t++bOLPHqb1ui5Gg+w0vuhEoAg7B__1XpgT8xzi4MF2sA@mail.gmail.com>
=?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?= <me@komzpa.net> writes:
> But what would be the scenario of failure if we have an implicit cast from
> jsonb datatype (that likely already parsed the number internally, or knows
> it holds non-numeric value) to numeric, that returns an error if it's
> unable to perform the conversion?
One fundamental problem is this: what's special about a cast to numeric?
There's no reason to assume that a jsonb field is more likely to be
numeric than anything else. But basically you only get to have one of
these. If we put this in, and then somebody else comes along and proposes
an implicit cast to text, or an implicit cast to timestamp, then
everything is broken, because the parser will no longer be able to
resolve max(jsonb) --- it won't know which implicit cast to apply.
Another fundamental problem is that implicit casts mask mistakes.
If there's an implicit cast to numeric, that applies everywhere not
only where it was what you meant. For some context on this you might
go back to the archives around the time of 8.3, where we actually
removed a bunch of implicit casts because they led to too many
surprising behaviors. Restricting implicit casts to the same type
category is a rule-of-thumb for reducing the potential surprise factor.
The cast notation is important here because it lets/makes the user
specify which semantics she wants for the converted value. I think
it's about equally likely for people to be converting JSON fields to text,
or (some form of) numeric, or datetime, so I don't think it's appropriate
to privilege one of those over all others.
> What would be other options, if not implicit cast?
Explicit casts, ie (jsonvar->'fieldname')::numeric. Yeah, you have
to write a bit more, but you don't get surprised by the way the
parser interpreted your query.
The other thing you can potentially do is use a variant operator
name, as we did for text output with ->>. But that doesn't scale
to very many result types, because it's impossible to choose
readily mnemonic operator names. So I'd stick with the casts.
regards, tom lane
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: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, me@komzpa.net, n.gluhov@postgrespro.ru, a.lubennikova@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Cast jsonb to numeric, int, float, bool
In-Reply-To: <8775.1520873000@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox