PostgreSQL 17: JSON_TABLE(), MERGE with RETURNING, and Updatable Views | Andrew Atkinson - Software Engineer, Author, High Performance PostgreSQL for Rails

submited by

Style Pass

2024-10-18 05:30:01

As a mature database system, prized for reliability, stability, and backwards compatibility, new features aren’t often the most splashy. However, there are still goodies that could become new tools in the toolboxes of data application builders.

In this post, we’ll pick out three items, and create some runnable examples with commands that can be copied and pasted into a Postgres 17 instance.

As an aside: for macOS, if you’re interested in using pg_upgrade, please see the post in-place upgrade from Postgres 14 to 15 as an example on how to upgrade your locally installed, earlier version.

Postgres supports SQL/JSON, which is like a selector style expressional language that provides methods to extract data from JSON.

SQL/JSON path expressions specify item(s) to be retrieved from a JSON value, similarly to XPath expressions used for access to XML content.

When we combine SQL/JSON expressions with a new function JSON_TABLE(), we can do powerful transformations of JSON text data into query results that match what you’d get from a traditional table.