change JSON serialization for BIGINT?

вт, 26 нояб. 2024 г. в 14:34, Tim McLaughlin <tim@gotab.io>:

Is there a way to have Postgres serialize BIGINT as a string rather than
number in JSON? By default it does this:

select row_to_json(row(500::bigint));
row_to_json
-------------
{"f1":500}

But I want it to do this (note that "500" is quoted):

select row_to_json(row(500::bigint));
row_to_json
-------------
{"f1":"500"}

Will this work?

select row_to_json(row(500::text));

--
Victor Yegorov

Thanks for the idea.  We could do that, but that would require code changes to our thousands of lines of SQL that generate JSON to add the explicit cast to text.  I am hoping not to have to do that.

There are numerous places where I would think this would be useful:
BIGINT or NUMERIC overflows Javascript Number Max Integer (my problem) with the default JSON parser in NodeJS
DECIMAL / NUMERIC value loses precision due to being ingest as a JSON Number which is annotated in high precision using a standard JSON parser

There are probably others, but the point is that there are cases where it would be great to override the default JSON serialization of Postgres scalars rather than have to go explicitly cast them all to text first.

--
Tim McLaughlin