--- title: "SQL/JSON borne" publication: "Postgres Weekly" issue: 574 published_at: "2024-10-16" canonical_url: "https://postgresweekly.com/issues/574" --- Plus Tom Lane's epic interview, recursive triggers, and suggested data types for different use cases. | #​574 — October 16, 2024 [Web Version](https://postgresweekly.com/issues/574) 📝 We're taking next week off, so the next issue of Postgres Weekly will be with you on Tuesday, October 30 – see you then! __ _Peter Cooper, your editor_ Together with [![pgAnalyze\](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/lhwcvgc1asd4fzaahty4.png)](https://pganalyze.com/tools/postgres-performance-check-list?utm_source=PostgresWeeklyExclusivePrimary10162024) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/q2dcgl4uk6v6jfvnbiwt.jpg)](https://www.crunchydata.com/blog/easily-convert-json-into-columns-and-rows-with-json_table) [Convert JSON into Columns and Rows with `JSON_TABLE`](https://www.crunchydata.com/blog/easily-convert-json-into-columns-and-rows-with-json_table) — New Postgres 17 feature alert! [JSON_TABLE](https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-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. Paul Ramsey [SQL/JSON is Here (Somewhat!)](https://www.depesz.com/2024/10/11/sql-json-is-here-kinda-waiting-for-pg-17/) — `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. Hubert depesz Lubaczewski [![\](https://copm.s3.amazonaws.com/7431c3ff.png)](https://pganalyze.com/tools/postgres-performance-check-list?utm_source=PostgresWeeklyExclusivePrimary10162024) [Get Proactive with Postgres Performance with This Monthly Check List](https://pganalyze.com/tools/postgres-performance-check-list?utm_source=PostgresWeeklyExclusivePrimary10162024) — 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. pganalyze sponsor [Trigger Recursion and How to Deal with It](https://www.cybertec-postgresql.com/en/dealing-with-trigger-recursion-in-postgresql/) — 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. Laurenz Albe **QUICK BITS:** - 🎤 The _Talking Postgres_ podcast [caught up with prolific Postgres contributor Tom Lane](https://talkingpostgres.com/episodes/how-i-got-started-as-a-developer-in-postgres-with-tom-lane) to hear about how he got into programming, how the Postgres core team works, and more. - 📺 Aaron Francis has finally launched his huge [Mastering Postgres video course.](https://masteringpostgres.com/) It'll cost you, but the first 25 videos are free in return for an email address.. - 🇬🇷 Next week, [a European Postgres ecosystem summit](https://justatheory.com/2024/10/postgres-ecosystem-summit-eu/) will be taking place in Athens (coinciding with the start of [PostgreSQL Conference Europe 2024.](https://2024.pgconf.eu/)) - 🇩🇪 Andreas Scherbaum shares [an update on PostgreSQL Berlin's recent meetup.](https://andreas.scherbaum.la/post/2024-10-11_postgresql-berlin-october-2024-meetup/) - 🇳🇱 Meanwhile, Pavlo Golub [shares how PGDay Lowlands 2024 went](https://www.cybertec-postgresql.com/en/pgday-lowlands-2024-reflections/) last month. [Is `pg_dump` a 'Backup' Tool?](https://rhaas.blogspot.com/2024/10/is-pgdump-backup-tool.html) — I’ve used `pg_dump` to make backups, and probably so have you, but the docs seem [keen to stress](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=4f29394ea941f688fd4faf7260d2c198931ca797) it’s really an _export_ tool. Robert ponders the narrative. Robert Haas 📄 [Did You Notice Postgres's Enhanced Release Notes?](https://www.crunchydata.com/blog/enhanced-postgres-release-notes) Greg Sabino Mullane 📄 [Tuning the glibc Memory Allocator (for Postgres)](https://vondra.me/posts/tuning-the-glibc-allocator-for-postgres/) Tomas Vondra 📰 Classifieds Join [Redgate’s October 29th webinar](https://www.red-gate.com/hub/events/postgresql-101-stored-procedures-functions?utm_source=postresweekly&utm_medium=email&utm_term=16+October+classified+ad&utm_campaign=mc-202410-t2-sodl-ubi-xdb-gc-all%20) to learn the surprising differences in stored procedures & functions between PostgreSQL and SQL Server. --- 🧰 PostgreSQL is all you need for AI. Use [pgvectorscale](https://github.com/timescale/pgvectorscale/) to get 28x faster search, 75% cheaper vs popular vector databases. --- ✨ [Kestra automates your entire workflow orchestration](https://shortclick.link/1vyklz)—including ETL and real-time data processing. Scale effortlessly across Postgres and cloud stacks. 🛠 Code and Tools [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/aijivbzv57ibiilnxehn.jpg)](https://masteringpostgres.com/types) [Postgres Type Explorer: See Data Types for Various Use Cases](https://masteringpostgres.com/types) — 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. Aaron Francis [PGroonga: Extend Full Text Search Features to All Languages](https://pgroonga.github.io/) — Brings the [Groonga](https://groonga.org/) 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. PGroona Project [Greenmask: Postgres Data Masking and Obfuscation Tool](https://docs.greenmask.io/latest/) — A Go-powered utility for dumping a Postgres database and anonymizing the data in a deterministic way. [GitHub repo.](https://github.com/GreenmaskIO/greenmask) Greenmask - [pgrx 0.12.6](https://github.com/pgcentralfoundation/pgrx/releases/tag/v0.12.6) – A way to build Postgres extensions with Rust. - [tbls 1.78](https://github.com/k1LoW/tbls) – Auto document a database in Markdown format. - [Prisma 5.21](https://github.com/prisma/prisma/releases/tag/5.21.0) – Popular ORM for Node.js and TypeScript. - [SeaORM 1.1](https://github.com/SeaQL/sea-orm) – A dynamic, async ORM for Rust. - [ParadeDB 0.11](https://github.com/paradedb/paradedb) – Postgres for search and analytics. - [pg-promise 11.10](https://github.com/vitaly-t/pg-promise) – Postgres interface for Node.js. **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|*).