--- title: "JSON Postgresly" publication: "Postgres Weekly" issue: 489 published_at: "2023-01-18" canonical_url: "https://postgresweekly.com/issues/489" --- Plus more on Postgres's wire protocol, why usql is a neat Postgres client, and the role of RETURNING. | #​489 — January 18, 2023 [Web Version](https://postgresweekly.com/issues/489) Together with [![Percona logo\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_350,h_100/e_make_transparent/co_white,e_outline:7/egbfiqexzspzz9grnyef.png)](https://www.percona.com/ha-for-postgresql?utm_campaign=22q2-cm-campaign-pg-high-availability&utm_source=postgres-weekly&utm_medium=primary-newsletter&utm_content=struggling-with-ha) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/kjwz7zcb2vjrrnehs7dk.jpg)](https://www.depesz.com/2023/01/11/waiting-for-postgresql-16-invent-random_normal-to-provide-normally-distributed-random-numbers/) [PostgreSQL 16's `random_normal()` Function](https://www.depesz.com/2023/01/11/waiting-for-postgresql-16-invent-random_normal-to-provide-normally-distributed-random-numbers/) — This will give us a way to get _normally-distributed_ random numbers, which could have key benefits in creating certain types of random/dummy data. Hubert gives us a tour. Hubert depesz Lubaczewski [How to JSON in Postgres](https://ftisiot.net/postgresqljson/main/) — A series of articles on how to solve common JSON related tasks from [extracting fields](https://ftisiot.net/postgresqljson/how-to-extract-field-from-json-postgresql/) and [converting a table row to JSON](https://ftisiot.net/postgresqljson/how-to-convert-table-row-json-postgresql/) to [prettifying JSON output](https://ftisiot.net/postgresqljson/how-to-prettify-json-output-in-postgresql/) and [removing null values from objects.](https://ftisiot.net/postgresqljson/how-to-remove-nulls-from-json-postgresql/) Francesco Tisiot [![\](https://copm.s3.amazonaws.com/12641c1c.png)](https://www.percona.com/ha-for-postgresql?utm_campaign=22q2-cm-campaign-pg-high-availability&utm_source=postgres-weekly&utm_medium=primary-newsletter&utm_content=struggling-with-ha) [Struggling with High Availability on Postgres?](https://www.percona.com/ha-for-postgresql?utm_campaign=22q2-cm-campaign-pg-high-availability&utm_source=postgres-weekly&utm_medium=primary-newsletter&utm_content=struggling-with-ha) — Percona has been building highly available PostgreSQL for over 15 years. Our architectures, powered by streaming replication and built with only battle-tested open source components, are designed to ensure an HA level of up to 99.9%. [Learn more](https://www.percona.com/ha-for-postgresql?utm_campaign=22q2-cm-campaign-pg-high-availability&utm_source=postgres-weekly&utm_medium=primary-newsletter&utm_content=struggling-with-ha). Percona sponsor [usql: A 'Universal `psql`'?](https://www.cybertec-postgresql.com/en/usql-universal-psql/) — A comparison of the Go-powered [usql](https://github.com/xo/usql) ‘universal CLI’ for various database systems (including Postgres) and Postgres’s own `psql` client. Syntax highlighting is.. a _highlight_, says Pavlo, but being able to query across multiple data sources is useful, and usql supports many of the same backslash commands too. Pavlo Golub [Using `RETURNING` to Return Modified Rows](https://sqlfordevs.com/returning-modified-rows) — Did you know that `INSERT`, `UPDATE`, and `DELETE` all support an optional `RETURNING` clause to return data from modified rows? Tobias Petry **IN BRIEF:** - [A look at six interesting patches](https://www.timescale.com/blog/a-postgresql-developers-perspective-six-interesting-patches-from-novembers-commitfest/) from a recent [Postgres Commitfest.](https://commitfest.postgresql.org/) - If you're free on April 20, _PG Day Chicago_ is [looking for volunteers](https://hdombrovskaya.wordpress.com/2023/01/17/pg-day-chicago-volunteer-sign-up/) to help at the event. - Greg Sabino Mullane is [continuing to solve Advent of Code 2022 challenges](https://www.crunchydata.com/blog/solving-advent-of-code-2022-using-postgres-day-12) using Postgres. Always something to learn from these. - PostGIS isn't _all_ about maps and geospatial work, you can [have fun with letters and type](https://www.crunchydata.com/blog/fun-with-letters-in-postgis-33) with it too. [Building a Postgres Wire Protocol Server](https://gavinray97.github.io/blog/postgres-wire-protocol-jdk-21) — A Java-oriented tutorial that has two purposes, both to show to implement Postgres’s [wire protocol](https://www.postgresql.org/docs/8.1/protocol.html) _and_ to show off a variety of new Java 17 features. Gavin Ray [Avoiding Constraint Violations with the `DATE` Type when Migrating from Oracle to Postgres](https://www.migops.com/blog/avoiding-constraint-violations-while-migrating-oracle-to-postgresql-date-data-type/) Akhil Reddy Banappagari [Handling NULLs and Empty Values in PL/Perl](https://fluca1978.github.io/2023/01/17/PLPerlNullHandling.html) Luca Ferrari 🛠 Code and Tools [SQLite-Based Databases on the Postgres Protocol? Yes We Can](https://blog.chiselstrike.com/sqlite-based-databases-on-the-postgres-protocol-yes-we-can-358e61171d65) — We linked to this project last week, but now there’s a bit more of an explainer. Glauber Costa [Bun 1.1.10: Simple, Performant ORM for Go](https://bun.uptrace.dev/) — Supporting Postgres, MySQL, SQL Server and SQLite, Bun is a modern SQL-first database client library for Go developers with built-in migration and fixtures support. [GitHub repo.](https://github.com/uptrace/bun) Vladimir Mihailenco [Tuple, a Lightning-Fast Pairing Tool Built for Remote Developers](https://tuple.app?utm_source=cooperpress&utm_medium=newsletter&utm_campaign=sponsorship-blitz&utm_content=Postgres+Weekly_Brief+Link) Tuple sponsor - ⭐️ [pgFormatter 5.4](https://www.postgresql.org/about/news/pgformatter-54-released-2569/) ↳ Advanced SQL and PL/pgSQL formatter. - [Prisma 4.9](https://github.com/prisma/prisma) ↳ Popular ORM and database toolkit for Node.js and TypeScript. - [pREST (PostgreSQL REST) 1.2](https://github.com/prest/prest) ↳ Go-powered low-code RESTful server for Postgres databases. - [pg_activity 3.0.3](https://github.com/dalibo/pg_activity) ↳ `top`-like app for server activity monitoring. - [data-diff 0.3.2](https://github.com/datafold/data-diff) ↳ Diff data in or across relational databases. - [Skunk 0.5](https://tpolecat.github.io/skunk/) ↳ Postgres library for Scala. - [pgx 0.7.0 Beta 1](https://github.com/tcdi/pgx) ↳ Build Postgres extensions with Rust. **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)