Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: John Scalia <jayknowsunix@gmail.com>
Mime-Version: 1.0 (1.0)
Date: Tue, 30 Aug 2022 11:21:26 -0400
Subject: Two PostgreSQL instances returning different output for same query 
Message-Id: <C1D9F597-B620-43B4-8561-49A8725BCBA1@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/C1D9F597-B620-43B4-8561-49A8725BCBA1%40gmail.com>
Precedence: bulk

I=E2=80=99m a bit stumped on this one. For one of my application teams both t=
heir DEV instance and UAT instance are running pg version 12.3 in AWS RDS, a=
nd I=E2=80=99ve used the same psql version 13.4.

The query is a simple:  SELECT to_jsonb(geom) FROM addresses LIMIT 1;

On both databases the geom field is of data type geometry from postgis, but t=
he two dbs are running different versions of Postgis. DEV is 3.0.2 while UAT=
 is 2.5.2, but I don=E2=80=99t think this is causing the issue.

Running the query on the DEV instance produces=20
to_jsonb
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94-
{=E2=80=9Ctype=E2=80=9D: =E2=80=9CPoint=E2=80=9D, =E2=80=9Ccoordinates=E2=80=
=9D: [-106.54569, 31.82908]}

While running it on the UAT instance produces
to_jsonb
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94
=E2=80=9C0101000020E610009000000000000000=E2=80=9C

If I remove the to_jsonb() so the query becomes SELECT geom FROM addresses; =
 both output the same very long string that the UAT instance produces for th=
e to_jsonb() output. So UAT does not seem to be processing the to_jsonb.

I=E2=80=99m thinking that there might be a GUC controlling the output format=
, but I can=E2=80=99t seem to find any references for one.

Any ideas?
Jay

Sent from my iPad=