Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #18677: numeric values in arrays are stored incorrectly

BUG #18677: numeric values in arrays are stored incorrectly

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      18677
Logged by:          marcel r
Email address:      marcel@vybenetwork.com
PostgreSQL version: 17.0
Operating system:   Linux
Description:

Hi,

I'm working with 256 bit unsigned integers, currently stored as NUMERIC
type.
These values work as individual values, but odd behaviour occurs when the
values are put in an array.

Please see below for the reference.
This was tested on Postgres v17 and v14 with the same behaviour in both.

I noticed that I'm able to convert from text (such as JSONB) to NUMERIC /
uint_256 with no problem.
But the moment these values are inside an array there is some process
involved that sets the numeric value's last digits to 0.

SELECT
    jsonb_path_query('605814335005781813025241542787157911726959411820757128006101511735853521072'::JSONB,
'$.number()') AS jsonb_path_value,  -- Works.
    ARRAY['605814335005781813025241542787157911726959411820757128006101511735853521072']::NUMERIC[]
as direct_numeric_array_outer,  -- Inaccurate.
    ARRAY['605814335005781813025241542787157911726959411820757128006101511735853521072'::NUMERIC]
as direct_numeric_array_inner,  -- Inaccurate.
    '605814335005781813025241542787157911726959411820757128006101511735853521072'::NUMERIC
as direct_numeric;  -- Works.

{
        "jsonb_path_value" :
"605814335005781813025241542787157911726959411820757128006101511735853521072",
        "direct_numeric_array_outer" :
"{605,814,335,005,781,800,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000}",
        "direct_numeric_array_inner" :
"{605,814,335,005,781,800,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000}",
        "direct_numeric" :
605814335005781813025241542787157911726959411820757128006101511735853521072
}

Is it not possible to store these values in an array accurately?

Thank you.

Re: BUG #18677: numeric values in arrays are stored incorrectly

From
David Rowley
Date:
On Tue, 29 Oct 2024 at 22:01, PG Bug reporting form
<noreply@postgresql.org> wrote:
> SELECT
>         jsonb_path_query('605814335005781813025241542787157911726959411820757128006101511735853521072'::JSONB,
> '$.number()') AS jsonb_path_value,  -- Works.
>         ARRAY['605814335005781813025241542787157911726959411820757128006101511735853521072']::NUMERIC[]
> as direct_numeric_array_outer,  -- Inaccurate.
>         ARRAY['605814335005781813025241542787157911726959411820757128006101511735853521072'::NUMERIC]
> as direct_numeric_array_inner,  -- Inaccurate.
>         '605814335005781813025241542787157911726959411820757128006101511735853521072'::NUMERIC
> as direct_numeric;  -- Works.
>
> {
>                 "jsonb_path_value" :
> "605814335005781813025241542787157911726959411820757128006101511735853521072",
>                 "direct_numeric_array_outer" :
> "{605,814,335,005,781,800,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000}",
>                 "direct_numeric_array_inner" :
> "{605,814,335,005,781,800,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000}",
>                 "direct_numeric" :
> 605814335005781813025241542787157911726959411820757128006101511735853521072
> }
>
> Is it not possible to store these values in an array accurately?

Which client are you using here? The thousands separator isn't
something we add to the numeric output, so something else must be
doing that. I suspect whatever third-party client you're using is
what's causing the problem.

Do you have any issues with psql?

David

Re: BUG #18677: numeric values in arrays are stored incorrectly

From
David Rowley
Date:
On Wed, 30 Oct 2024 at 09:59, Marcel Roux <marcel@vybenetwork.com> wrote:
>
> Hi David,
>
> Thanks for your response.
>
> I just checked with psql and it seems the results are indeed correct.
> I further checked the results from my procedures, and these are also displaying correctly in psql.
>
> Output in psql:
>
>                               jsonb_path_value                               |
direct_numeric_array_outer                          |                          direct_numeric_array_inner
 
>
-----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------
>  605814335005781813025241542787157911726959411820757128006101511735853521072 |
{605814335005781813025241542787157911726959411820757128006101511735853521072}|
{605814335005781813025241542787157911726959411820757128006101511735853521072}
> (1 row)
>
> I will raise the issue with the DBeaver support.
>
> Thank you for your time.
>
> Marcel

Thanks for checking. I've included the mailing list so the archives
have a copy of your response.

David