Question 289922 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL negative "-0.0" in jsonb[] field

I have an "issue" (probably coming from my understanding of the json, jsonb and jsonb[] types) which is the following:

My team and I insert 3D coordinates records into a table whose column type is jsonb[].

The table contains an id column, that is based on the text representation of the records.
With that said, when I select some data from the table, all the records containing coordinates whose values where equal to -0.0 (bit sign on), the data I get back does not contain the minus sign. -0.0 -> 0.0.

I am aware of the IEEE754 RFC on floating point numbers (and the comparison section), I was wondering, how does Postgres treat the elements inside the jsonb array?
Is there a conversion/casting done on the fly when inserting the data.
Is there a way to deactivate such behaviour?

Minimal reproducible code:

CREATE TABLE IF NOT EXISTS "my_test_table" (
    coordinates jsonb[]
);

TRUNCATE TABLE "my_test_table";


INSERT INTO 
    "public"."my_test_table" ("coordinates")
VALUES (
ARRAY[jsonb_build_array(-0.0, 0.0,0.0)]::jsonb[]
);


select * from my_test_table;

The output is:

{"[0.0, 0.0, 0.0]"}

when one expects:

{"[-0.0, 0.0, 0.0]"}
Tags:

2

Answers


  1. 0

    PostgreSQL stores numbers as numeric in jsonb. See this code in src/backend/utils/adt/jsonb.c:

    static JsonParseErrorType
jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
{
    [...]

    switch (tokentype)
    {
        [...]
        case JSON_TOKEN_NUMBER:

            /*
             * No need to check size of numeric values, because maximum
             * numeric size is well below the JsonbValue restriction
             */
            Assert(token != NULL);
            v.type = jbvNumeric;
            if (!DirectInputFunctionCallSafe(numeric_in, token,
                                             InvalidOid, -1,
                                             _state->escontext,
                                             &numd))
                return JSON_SEM_ACTION_FAILED;
            v.val.numeric = DatumGetNumeric(numd);
            break;
        [...]

    Data type numeric has no notion of “negative zero”:

    SELECT -0::float8 AS float8, -0::numeric AS numeric;

 float8 │ numeric 
════════╪═════════
     -0 │       0
(1 row)

    numeric is stored as binary coded decimal.

    Login or Signup to reply.
  2. 0

    Addressing the other question:

    Is there a way to deactivate such behaviour?

    Yes, there is. At a price of everything jsonb offers over json: demo at db<>fiddle

    select a::json "json",
       a::jsonb "jsonb" 
from (values ('[-0.0,0.0,0.0]'))_(a);
    json jsonb
    [-0.0,0.0,0.0] [0.0, 0.0, 0.0] 
    create table test as 
select a::json "json",
       a::jsonb "jsonb" 
from (values ('[-0.0,1.1,2.2]'))_(a);

select 'json',
       json->>0 "->>",
       json->0 "->",
       pg_typeof(json->0),
       json->0 is json scalar AS "is json scalar",
       json_typeof(json->0)
from test
union all
select 'jsonb',
       jsonb->>0,
       (jsonb->0)::json,
       pg_typeof(jsonb->0),
       jsonb->0 is json scalar,
       jsonb_typeof(jsonb->0)
from test;
    ?column? ->> -> pg_typeof is json scalar json_typeof
    json -0.0 -0.0 json t number
    jsonb 0.0 0.0 jsonb t number

    If you need the jsonb functionality and the fields to be of number type while holding onto the bit sign for some other logic, you can save it under a separate, appropriately named key, or another array of boolean values that hold corresponding "source" bit signs of your coordinates: demo2

    TRUNCATE TABLE "my_test_table";
alter table "public"."my_test_table" add column "source_bit_signs" jsonb[];

INSERT INTO "public"."my_test_table" ("coordinates","source_bit_signs")
select ARRAY[jsonb_build_array(x::numeric,y::numeric,z::numeric)]::jsonb[],
       ARRAY[jsonb_build_array(x~'-.*',y~'-.*',z~'-.*')]::jsonb[]
from (values ('-0.0', '0.0','0.0'))_(x,y,z)
returning *;
    coordinates source_bit_signs
    {"[0.0, 0.0, 0.0]"} {"[true, false, false]"}

    That of course forces you to bind those coordinates as string types instead of numeric types.

    You can also not use number-type fields, and save the coordinates as string fields in jsonb. The size, indexing and JSONPath processing won’t be the same, but other than that, most in-db type mapping on json/jsonb output goes through text type first anyways, before you get to interact with the field for the purpose of a query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search