Even JSONB In Postgres Needs Schemas | POSETTE 2024

Video of a conference talk about schemas and JSONB in Postgres presented by Chris Ellis at POSETTE: An Event for Postgres 2024. PostgreSQL offers great support for JSON, which has been a game changer for me in a number of projects and use cases, offering easy solutions for those times where you really need to store complex structures, or the unknowns.

However often you still need to be in control of what is being stored, or at least requiring some shape at minimum.

Thankfully PostgreSQL has a great range of features for ensuring data integrity and making our future lives easier.

We'll take a brief overview of PostgreSQL's JSON support, along with some practical and real world examples of when and how you can use it.

Then we'll look at how you can control those unknown unknowns with JSON Schema and CHECK constraints.

Chris is a computing and electronics geek, who loves working with open source software. He is a jack of all trades consultant usually spending his time engineering systems with PostgreSQL, Java and Linux. Now and then he gets time to design some electronics often involving ESP32s, Raspberry Pi CM4s and LED badges.

Chapters:
00:00 - Intro
02:19 - Handling complex or unknown data
03:58 - PostgreSQL JSON support
09:39 - Things can go wrong
11:52 - Finding the type
13:00 - Check constraints
14:29 - Structure
17:50 - Functions to the rescue
19:57 - JSON schemas

📕 Everything you need to know about POSETTE: An Event for Postgres 2024 can be found at: https://aka.ms/posette

✅ Learn more:
Watch more POSETTE talks: https://aka.ms/posette-playlist-2024

📌 Let’s connect:
X – @PosetteConf https://x.com/PosetteConf
Mastodon - @posetteconf https://mastodon.social/@posetteconf
Threads – @posetteconf https://www.threads.net/@posetteconf

🔔 Subscribe to the POSETTE News: https://aka.ms/posette-subscribe

📣 Slides for this talk: https://nexteam.co.uk/posette_even_js...

#PosetteConf #PostgreSQL #JSON

Presenters:
Chris Ellis, Nexteam

Connect:
Chris Ellis: https://bergamot.social/@intrbiz,   / chris-ellis-29177a41