Craig Kerstiens
Craig Kerstiens
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? All of our curated tutorials exist directly on the main Crunchy Data Developer area . However...
Read MorePaul Ramsey
Paul Ramsey
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 function. The function returns a double precision float in a continuous uniform distribution between 0.0 and 1.0. What does that mean? It means that you c...
Read MorePaul Ramsey
Paul Ramsey
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 • Time Zones • South America • Greenland Special • Madagascator Liquid Resize Time Zones South America Greenland Special Madagascator The last two are just applications of common map projections with very uncommon projection parameters that accentuate certain areas of the globe, a c...
Read MorePaul Ramsey
Paul Ramsey
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 h...
Read MoreCraig Kerstiens
Craig Kerstiens
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 . • Can Postgres become an autonomous database with AI? Maybe. • Should you use AI to help you troubleshoot issues? Perhaps, but better left to experts. • Should I avoid AI when working with my database? Not at all! Can I use Postgres for...
Read MoreJacob Coblentz
Jacob Coblentz
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 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. Here's a simple example to get...
Read MorePaul Ramsey
Paul Ramsey
By combining triggers, the PostgreSQL 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 converts events from the PostgreSQL event bus to standard WebSockets messages that any web client can handle. For multi-user real-time applications (like a fleet tracker, or auction system, for example), this setup can be a boon! The database is the central source-of-truth, and all ap...
Read MoreCraig Kerstiens
Craig Kerstiens
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. Let's take a look at a few of my favorites. First, let's start with one of the most simple built-in ones but when doing things with time is quite handy. Want to know what time it is according to your database? Want to know the biggest date in your data? Now let's look at one that is a little bit more int...
Read More