#​467 — August 10, 2022

Web Version

☀️ If you don't receive Postgres Weekly next week (August 17), don't panic. Your email is (probably) still working – we're just taking a week off for summer vacation :-) We'll be back, complete with sun tan wind burn, on August 24.
Peter Cooper, your editor.

Postgres Weekly

A blue elephant in a DVD rental store.

Pagila 3.0: A Sample Database for Postgres — Originally a port of the ‘Sakila’ example database for MySQL, Pagila provides a complete and realistic schema and data that could be used in tutorials, demos, for learning SQL, or testing. The DVD rental store metaphor from Sakila stays, but numerous extra features have been brought into play, including JSONB columns, foreign keys, and triggers. There are full instructions on spinning it up too.

Devrim Gündüz

Build Internal Tools with PostgreSQL — Connect Retool to Postgres in a few minutes, and quickly build internal tools on top of your Postgres data. Retool connects directly to your PostgreSQL DB and lets you use SQL to manage the data in your PostgreSQL tables. Start building with PostgresSQL.

Retool sponsor

HAVING: A Less Understood SQL Clause?HAVING provides WHERE-like predicate-based filtering of results but of aggregations or groups and most commonly following a GROUP BY clause (something WHERE cannot do). In Hacker News’s discussion of this item, more people were curious to learn about the use of GROUP BY CUBE in the article though!

Small Things SQL

Validating Postgres Results and Inferring Query Static Types — A quick explainer of how Zod and Slonik come together to let you write schemas and get static types with runtime result validation when working with Postgres from Node.js.

Gajus Kuizinas


What is Time-Series Data, and Why Do You Need a Time-Series Database?TimescaleDB is an open source extension bringing powerful time-series features to Postgres, but if you’re still unsure why and when time-series features make sense, this primer could help.

Kulkarni, Booz and Toth (Timescale)

Weekly Postgres Videos by pganalyze: Subscribe to “5mins of Postgres”

pganalyze sponsor

⬆️ Editor's note: I hate breaking the editorial wall, but this YouTube channel is fantastic as it essentially focuses on one small topic or blog post at a time. If you get bored next week and miss Postgres Weekly, it'll give you something to watch! :-)

Accelerating Spatial Postgres: Varnish Cache for pg_tileserv using Kustomize
Rekha Khandhadia (CrunchyData)

Running Postgres on NFS with CentOS 7
Cary Huang

🛠 Code and Tools

DbGate: Another Database Desktop Client App — Built as an Electron app using Svelte as the underlying framework, this is a pretty elegant cross platform (and open source!) client for Postgres, MySQL, and others.

Jan Prochazka

pgFormatter 5.3: A Postgres SQL Syntax Beautifier — Mostly a bug-fix release. There’s an online demo here and it can work with SQL generally, not just Postgres’s dialect.

Gilles Darold


PostGIS 3.3.0-rc1
Bytebase 1.3 – Database schema change and versioning for teams.
Prisma 4.2 – Next-generation ORM for Node.js.
sqlc 1.15 – Generate type-safe Go code from SQL.
usql 0.12 – Universal CLI for SQL databases.
FerretDB 0.5.2 – MongoDB-on-Postgres. Kinda.
npgsql 6.0.6 – .NET data provider for Postgres.
Supabase Realtime 0.23 – Realtime Postgres changes over WebSockets.
PLV8 3.1.3 – Use JS as a procedural language via V8.