~postgresweekly | Issue 574 - SQL/JSON borne (8)

Convert JSON into Columns and Rows with JSON_TABLE — New Postgres 17 feature alert! JSON_TABLE is a function that lets you query JSON data and get results in a typical, relational view. Paul shows us a practical use case.

SQL/JSON is Here (Somewhat!) — JSON_TABLE (above) is great, but makes up just one part of the SQL/JSON experience available. SQL/JSON is a broad term for a path language, query functions, and operators for working with JSON data from SQL, and here we see more of what Postgres 17 brings to the JSON table.

Get Proactive with Postgres Performance with This Monthly Check List — Staying on top of PostgreSQL maintenance can be challenging. Our monthly check list guides you through critical tasks to maintain performance, reliability, and uptime—quickly and effectively. Download your free check list today.

Trigger Recursion and How to Deal with It — If you’ve set up a trigger whose behavior results in the same trigger being triggered, you’re in for a lot of potentially tricky to debug problems. Luckily, it’s trivial to tweak triggers to only run when intended.

Is pg_dump a 'Backup' Tool? — I’ve used pg_dump to make backups, and probably so have you, but the docs seem keen to stress it’s really an export tool. Robert ponders the narrative.

Postgres Type Explorer: See Data Types for Various Use Cases — As part of the launch of Mastering Postgres, Aaron Francis has unveiled an online tool that offers recommendations of which data types to use for different situations (e.g. storing hashes, monetary amounts, UUIDs, binary data). It also gives indications of how much storage space each could use.

PGroonga: Extend Full Text Search Features to All Languages — Brings the Groonga fulltext search engine to Postgres which can suit certain use cases better than the Postgres default, such as with Japanese or Chinese. Now supports Postgres 17 as of v3.2.4.

Greenmask: Postgres Data Masking and Obfuscation Tool — A Go-powered utility for dumping a Postgres database and anonymizing the data in a deterministic way. GitHub repo.