Why does to_jsonb() convert bigint to number in postgresql?

Question

I am using KnexJS to fetch data from PostgreSQL and in some places, I use the following pattern:-

select to_jsonb(s.*) as student, to_jsonb(st.*) as subject_test
from subject_tests st
inner join students s on st.student_id = s.id
where st.date >= '...'

The problem I am facing is that if the id column in subject_test table is a bigint, I get it back as a number in the subject_test object. This can be inaccurate since bigint is able to store beyond the range of javascript number type. Why does to_jsonb not convert bigint to string in json instead? Is there a way to fix this?

Because JSON can represent arbitrarily large numbers. Only JavaScript's number type cannot, and `JSON.parse` doesn't care. See the [various](https://github.com/tc39/proposal-bigint/issues/162) [discussions](https://stackoverflow.com/q/18755125/1048572) about how to parse JSON to native js `BigInt`s. — Bergi, May 19 '21 at 09:51

score 1 · Answer 1 · answered May 19 '21 at 09:28

You can use casting operator :: to convert to bigint to text

Example:

create table test(id bigint, name varchar);
insert into test values(1,'A');
insert into test values(2,'B');


select to_jsonb(t1) from test t1

to_jsonb
----------------------
{"id": 1, "name": "A"}
{"id": 2, "name": "B"}



select to_jsonb(t1.*) from (
select id::text,name from test) t1


to_jsonb
{"id": "1", "name": "A"}
{"id": "2", "name": "B"}

Why does to_jsonb() convert bigint to number in postgresql?

1 Answers1