Incorrect insert of bigint into JSONB column when using pgAdmin 4

Question

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)

Works for me: https://rextester.com/VIJU78988 and https://i.imgur.com/milov5K.png - maybe your SQL client causes this? — , Apr 03 '19 at 09:00
what postgres version do you run? Issue is not only client as actual writes from application are also wrong. — iwek, Apr 03 '19 at 09:08
The screenshot was done using Postgres 11 on Windows. The example on rextester uses Postgres 9.6. Same results on dbfiddle.uk with [Postgres 11](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=65cf86f0467d1c3944644826eec2384e) and [Postgres 10](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=65cf86f0467d1c3944644826eec2384e). I would strongly suspect a bug in your application. — , Apr 03 '19 at 09:14
Just to be entirely sure, are you looking at the correct record? Your first query has no conditions, but the version where you get the correct result gets it for a specific id - are the records you're looking at actually the same between those two queries? — 404, Apr 03 '19 at 09:28
Yes it is surely the same record. Actually when I execute code directly via psql tool it works as expected. Bug occurs in PGAdmin and application so it might be driver issue. — iwek, Apr 03 '19 at 09:38

score 0 · Accepted Answer · answered Apr 03 '19 at 11:52

0

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

answered Apr 03 '19 at 11:52

iwek

348
4
14

You should report that on the mailing list: https://www.pgadmin.org/support/list/ – Apr 03 '19 at 11:55

Incorrect insert of bigint into JSONB column when using pgAdmin 4

1 Answers1