Date: Tue, 26 Nov 2024 08:07:13 -0500
From: Tim McLaughlin <tim@gotab.io>
To: Victor Yegorov <vyegorov@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Message-ID: <e5d6c428-a9a7-438d-a573-b0afd03649c0@Spark>
In-Reply-To: <CAGnEbohqVqmA6gJgKmHqANMYHNtyAxv-3U3izEUVfoKoMZp-FA@mail.gmail.com>
References: <536dca0f-ef1a-4621-ac1f-9c1c1dd74fbd@Spark>
 <CAGnEbohqVqmA6gJgKmHqANMYHNtyAxv-3U3izEUVfoKoMZp-FA@mail.gmail.com>
Subject: Re: change JSON serialization for BIGINT?
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="6745c80a_5dc79ea8_1066"
Archived-At: <https://www.postgresql.org/message-id/e5d6c428-a9a7-438d-a573-b0afd03649c0%40Spark>
Precedence: bulk

--6745c80a_5dc79ea8_1066
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Thanks for the idea.=C2=A0=C2=A0We could do that, but that would require =
code changes to our thousands of lines of SQL that generate JSON to add t=
he explicit cast to text.=C2=A0=C2=A0I 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) wi=
th the default JSON parser in NodeJS
DECIMAL / NUMERIC value loses precision due to being ingest as a JSON Num=
ber 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 sc=
alars rather than have to go explicitly cast them all to text first.

--
Tim McLaughlin
On Nov 26, 2024 at 6:36=E2=80=AFAM -0500, Victor Yegorov <vyegorov=40gmai=
l.com>, wrote:
> =D0=B2=D1=82, 26 =D0=BD=D0=BE=D1=8F=D0=B1. 2024=E2=80=AF=D0=B3. =D0=B2 =
14:34, Tim McLaughlin <tim=40gotab.io>:
> > > Is there a way to have Postgres serialize BIGINT as a string rather=
 than number in JSON=3F=C2=A0=C2=A0By default it does this:
> > >
> > >
> > > select row=5Fto=5Fjson(row(500::bigint));
> > > =C2=A0row=5Fto=5Fjson
> > > -------------
> > > =C2=A0=7B=22f1=22:500=7D
> > >
> > > But I want it to do this (note that =22500=22 is quoted):
> > >
> > > select row=5Fto=5Fjson(row(500::bigint));
> > > =C2=A0row=5Fto=5Fjson
> > > -------------
> > > =C2=A0=7B=22f1=22:=22500=22=7D
>
> Will this work=3F
>
> select row=5Fto=5Fjson(row(500::text));
>
> --
> Victor Yegorov

--6745c80a_5dc79ea8_1066
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Thanks for the idea.&=23160;&=23160;We could do tha=
t, but that would require code changes to our thousands of lines of SQL t=
hat generate JSON to add the explicit cast to text.&=23160;&=23160;I am h=
oping not to have to do that.<br />
<br />
There are numerous places where I would think this would be useful:<br />=

BIGINT or NUMERIC overflows Javascript Number Max Integer (my problem) wi=
th the default JSON parser in NodeJS<br />
DECIMAL / NUMERIC value loses precision due to being ingest as a JSON Num=
ber which is annotated in high precision using a standard JSON parser<br =
/>
<br />
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 sc=
alars rather than have to go explicitly cast them all to text first.<br /=
></div>
</div>
<div name=3D=22messageSignatureSection=22><br />
<div dir=3D=22auto=22>--<br />
Tim McLaughlin</div>
</div>
<div name=3D=22messageReplySection=22>On Nov 26, 2024 at 6:36=E2=80=AFAM =
-0500, Victor Yegorov &lt;vyegorov=40gmail.com&gt;, wrote:<br />
<blockquote type=3D=22cite=22>
<div dir=3D=22ltr=22>
<div dir=3D=22ltr=22>=D0=B2=D1=82, 26 =D0=BD=D0=BE=D1=8F=D0=B1. 2024=E2=80=
=AF=D0=B3. =D0=B2 14:34, Tim McLaughlin &lt;<a href=3D=22mailto:tim=40got=
ab.io=22>tim=40gotab.io</a>&gt;:</div>
<div class=3D=22gmail=5Fquote=22>
<blockquote class=3D=22gmail=5Fquote=22 style=3D=22margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=22>
<div>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Is there a way to have Postgres serialize BIGINT as=
 a string rather than number in JSON=3F&=23160;&=23160;By default it does=
 this:<br />
<br />
<br />
select row=5Fto=5Fjson(row(500::bigint));<br />
&=23160;row=5Fto=5Fjson&=23160;<br />
-------------<br />
&=23160;=7B=22f1=22:500=7D<br />
<br />
But I want it to do this (note that =22500=22 is quoted):<br />
<br />
select row=5Fto=5Fjson(row(500::bigint));<br />
&=23160;row=5Fto=5Fjson&=23160;<br />
-------------<br />
&=23160;=7B=22f1=22:=22500=22=7D<br /></div>
</div>
<div name=3D=22messageSignatureSection=22></div>
</div>
</blockquote>
</div>
<div><br />
Will this work=3F<br />
<br />
select row=5Fto=5Fjson(row(500::text));<br />
<br /></div>
<span class=3D=22gmail=5Fsignature=5Fprefix=22>--</span><br />
<div dir=3D=22ltr=22 class=3D=22gmail=5Fsignature=22>
<div dir=3D=22ltr=22>
<div>Victor Yegorov</div>
</div>
</div>
</div>
</blockquote>
</div>
</body>
</html>

--6745c80a_5dc79ea8_1066--