Posts about Fun with Postgres

The Postgres playground started with a fun idea of "I wonder if I can put Postgres in a web browser". It evolved to a full set of guided tutorials that you can follow along step by step with. We actually use Notion to power all of our tutorials and an in-house API. This means a new tutorial is as simple as some SQL and some markdown. But, did you know you can bring your own setup to the playground?

Just Postgres in a browser

Generating random numbers is a surprisingly common task in programs, whether it's to create test data or to provide a user with a random entry from a list of items.

PostgreSQL comes with just a few simple foundational functions that can be used to fulfill most needs for randomness.

Almost all your random-ness needs will be met with the random()

Last week, Randall Munroe dropped his latest XKCD "Bad Map Projection", number six, "ABS(Longitude)", which looks like this:

Truly this is a bad map projection, on a par with the previous five:

• Liquid Resize

An extremely common problem in fast-moving data architectures is providing a way to feed ad hoc user data into an existing analytical data system.

Do you have time to whip up a web app? No! You have a database to feed, and events are spiraling out of control... what to do?

How about a Google Sheet? The data layout is obvious, you can even enforce things like data types and required columns using locking and protecting, and unlike an Excel or LibreOffice document, it's always online, so you can hook the data into your system directly.

Access Sheets Data Remotely

There's a lot of excitement around AI, and even more discussion than excitement. The question of Postgres and AI isn't a single question, there are a ton of paths you can take under that heading...

• Can I use Postgres for building AI related apps? Absolutely

Working at Crunchy Data on the spatial team, I'm always looking for new features and fun things to show on live demos. I recently started playing around with ST_Letters and wanted to jot down some quick code samples for playing around with this feature, introduced in PostGIS 3.3. These examples are super easy to use, they don't need any data!

The screenshots shown below came from pgAdmin's geometry viewer and will also work with other query GUI tools like QGIS or DBeaver.

ST_Letters

By combining triggers, the PostgreSQL LISTEN/NOTIFY system, and the pg_eventserv service, you can build a real-time application that keeps your web application state perfectly in sync with your database state.

pg_eventserv

Postgres is an awesome database for a lot of reasons, put simply it's a really feature rich, powerful, and reliable database. One of those rich areas is the many built-in functions in Postgres.