Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jun 18, 2020 at 11:51 AM Oleg Bartunov <obartunov@postgrespro.ru> wrote:
>> The problem is that we tried to find a trade-off between standard and postgres
>> implementation, for example, in postgres CAST allows NaN and Inf, and SQL Standard
>> requires .double should works as CAST.
> It seems like the right thing is to implement the standard, not to
> implement whatever PostgreSQL happens to do in other cases. I can't
> help feeling like re-using the numeric data type for other things has
> led to this confusion. I think that fails in other cases, too: like
> what if you have a super-long integer that can't be represented as a
> numeric? I bet jsonb will fail, or maybe it will convert it to a
> string, but I don't see how it can do anything else.
Actually, the JSON spec explicitly says that any number that doesn't fit
in an IEEE double isn't portable [1]. So we're already very far above and
beyond the spec's requirements by using numeric. We don't need to improve
on that. But I concur with your point that just because PG does X in
some other cases doesn't mean that we must do X in json or jsonpath.
regards, tom lane
[1] https://tools.ietf.org/html/rfc7159#page-6
This specification allows implementations to set limits on the range
and precision of numbers accepted. Since software that implements
IEEE 754-2008 binary64 (double precision) numbers [IEEE754] is
generally available and widely used, good interoperability can be
achieved by implementations that expect no more precision or range
than these provide, in the sense that implementations will
approximate JSON numbers within the expected precision. A JSON
number such as 1E400 or 3.141592653589793238462643383279 may indicate
potential interoperability problems, since it suggests that the
software that created it expects receiving software to have greater
capabilities for numeric magnitude and precision than is widely
available.
Note that when such software is used, numbers that are integers and
are in the range [-(2**53)+1, (2**53)-1] are interoperable in the
sense that implementations will agree exactly on their numeric
values.