Search code examples
postgresqlpgadmin

Incorrect insert of bigint into JSONB column when using pgAdmin 4

I am observing weird behavior while inserting jsonb values that has bigint field. According to doc jsonb supports numeric data type so it should not be an issue.

Table:

CREATE TABLE document_wrapper
(
    id integer NOT NULL,
    document jsonb NOT NULL,
    CONSTRAINT document_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Sample insert:

insert into document_wrapper(id, document) values(-8, '{"id":101861191707868275}');

Now when querying for document:

SELECT document FROM document_wrapper;

gives result (note 0 at the end):

{ "id": 101861191707868270 }

But when I select actual id value from it it is correct in every of thoses cases:

SELECT
    (document->'id')::text, 
    jsonb_extract_path_text(d.document, 'id') , 
    (document #>> '{id}')::bigint, 
    (document->>'id')::numeric, 
    (document->'id')::bigint
FROM document_wrapper d 
WHERE id = -8 ;

Result is 101861191707868275 in every case.

Why value visible in json is different than the one that was insered there in the first place? This causes issues while sending json to backend app that gets wrong value.

Posgres version: PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

Update: code runs correctly via psql tool. Issue occurs in pgAdmin and application (driver https://mvnrepository.com/artifact/org.postgresql/postgresql version 42.2.5)

Solution

  • So the issue was actually java-script. Issue manifested only in web applications (webapp pg_admin, application frontend). Passed number extended js-es Number.MAX_SAFE_INTEGER which caused number to be rounded. Silly of me to trust that backend returns bad data based on response looked up in browser (which was already rounded).

    As workaround I've changed number to string