Elizabeth Christensen
Elizabeth Christensen
Beyond a basic query with a join or two, many queries require extracting subsets of data for comparison, conditionals, or aggregation. Postgres’ use of the SQL language is standards compliant and SQL has a world of tools for subqueries. This post will look at many of the different subquery tools. We’ll talk about the advantages and use cases of each, and provide further reading and tutorials to dig in more. I’ll take a broad definition of “subquery”. Why am I calling all of these subqueries? The...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
We've also loaded a tutorial for Day 19's challenge if you want to try it with a pre-loaded data set. This article will contain spoilers both on how I solved 2022 Day 19's challenge "Not Enough Minerals" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. Day 19 tasks us with creating lots and lots of mini robots to gather resources and feed our herd of elephants (rescued a few days back ). We'll do...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres wasn't always the cool kid. It didn't emerge from millions of dollars of VC funding, it didn't have a team of DevRel evangelists championing it, it simply started and evolved. Postgres just passed the 25 year milestone as an open source project. With the upcoming release of Postgres 16 in coming months, we thought we'd pause for a minute and take a look back at Postgres and how it got to where it is today. If for some reason you've been living under a rock and are unfamiliar with Postgr...
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
In my last blog post , I showed four ways to access a remotely hosted CSV file from inside PostgreSQL: • Using the command with the option, • Using the http extension and some post-processing, • Using a PL/Python function, and • Using the ogr_fdw foreign data wrapper. Using the command with the option, Using the http extension and some post-processing, Using a PL/Python function, and Using the ogr_fdw foreign data wrapper. In this post, we are going to explore ogr_fdw a little...
Read MoreCraig Kerstiens
Craig Kerstiens
Today we're excited to announce several big improvements to Crunchy Bridge to make it easier to work with your database without having to become a DBA. Today we're releasing: • Database insights: Real-time indicators to ensure a healthy database • Metrics: Monitor your system over time • Production check: Ensure you're ready for launch day Database insights: Real-time indicators to ensure a healthy database Metrics: Monitor your system over time Production check: Ensure you're ready for launch...
Read MoreCraig Kerstiens
Craig Kerstiens
We are happy to unveil the newest release of Crunchy Postgres for Kubernetes version 5.4. This update brings an array of features set to improve your experience including: • Support for ARM • Native vector search via pgvector • Comprehensive support for huge pages • Native support for Postgres tablespaces • Documentation enhancements Support for ARM Native vector search via pgvector Comprehensive support for huge pages Native support for Postgres tablespaces Documentation enhancements To explo...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 18's challenge "Boiling Boulders" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. (heatmap slicing a 3-D piece of lava - all in SQL!) We've also loaded a tutorial for Day 18's challenge if you want to try it with a pre-loaded data set. Tech: 🐘 The ever important file_fdw 🐘 Using sequences as a crude numbering aid 🐘 A recursi...
Read MoreChristopher Winslett
Christopher Winslett
We have a lot of Postgres foreign data wrapper ( FDW ) users here at Crunchy. The postgres_fdw lets you connect to remote servers and in some cases can be an alternative for traditional ETL/ELT processes. As I see use of the Postgres foreign data wrapper expanding, I wanted to make some recommendations on how to approach performance. When you query from a single Postgres cluster, the database cleverly uses internal statistics to auto-tune performance . However, when querying from the Postgr...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
(Yes, this image was generated completely by SQL statements!) We've also loaded a tutorial for Day 17's challenge if you want to try it with a pre-loaded data set. This article will contain spoilers both on how I solved 2022 Day 17's challenge "Pyroclastic Flow" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. Another puzzle featuring elephants! (❤️ ❤️ ❤️). This time, the elephants are involved in...
Read More