Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Greg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 15's challenge "Beacon Exclusion Zone" 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. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformattin...
Read MoreCraig Kerstiens
Craig Kerstiens
For developers who have been around SQL long enough, they know that there is often more than one way to get the same result. Today I wanted to look at a few different ways to aggregate and show a total with paid and unpaid status. First, we will use a common table expression ( CTE ), which is a nice method for organizing subqueries. Second, we use statements within aggregation context to filter out the values we want. Lastly, we use to clean up the syntax, but effectively do the same as th...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres is a great database with a ton of features including really rich indexing . Postgres itself maintains all sorts of data under the covers about things like cache hits and misses, when indexes are and aren't used , and more. If you're staring at a complex explain plan you may think some well targeted indexes may help, but how can you be sure? Enter HypoPG , a Postgres extension for adding hypothetical indexes to Postgres so you can do index planning. HypoPG supports hypothetical inde...
Read MoreElizabeth Christensen
Elizabeth Christensen
Custom data types is one of those many features that makes PostgreSQL flexible for development of a huge variety of business and application use cases. Data types will help you primarily for data integrity, ensuring your data is stored in the database as you want it to be. A common surprise gift of using strict data types is that you can end up reducing your long term data maintenance. There’s two main ways to customize data types in Postgres: • Create s, which are value constraints added to bu...
Read MoreBen Blattberg
Ben Blattberg
Kubernetes was developed originally as an orchestration system for stateless applications. Today, Kubernetes is the backbone of countless full stack applications with, notably, a database as part of the stack. So, a question we often hear is: How can Kubernetes be the foundation of that most stateful application of all, the database? Let’s say you maintain a Postgres database and you’ve been tasked with moving it to Kubernetes. You can just start up a Kubernetes pod running a Postgres image and...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 14's challenge "Regolith Reservoir" 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. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting a...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 13's challenge "Distress Signal" 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. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 12's challenge "Hill Climbing Algorithm" 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. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatt...
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 MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 11's challenge "Monkey in the Middle" 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. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting...
Read More