Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Craig Kerstiens
Craig Kerstiens
If you are like us, Tailscale was an obvious tool to add to our network — as soon as we saw it, we knew we needed it. For those who haven’t tried Tailscale yet, it’s best described as a programmable, re-sizeable, distributed private network mesh. Yes, it’s that great. The better part is that Tailscale is fairly simple, and because it’s simple, it is easy to get correct. When we set out to build Crunchy Bridge we wanted to ensure you could build safely from day one. This included features suc...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 3's challenge "Rucksack Reorganization" 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 reformatti...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 2's challenge " Rock Paper Scissors " 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
Time for the annual Advent of Code challenge for 2022! If you are not familiar with AOC, every December a new puzzle is added each day, getting harder as it goes along, and the challenge is to solve it any way you can by transforming an input file into an answer. I'm going to use PostgreSQL and solve things solely with the use of SQL. This article will contain spoilers both on how I solved Day 1's challenge "Calorie Counting" , as well as general ideas on how to approach the problem. I recomm...
Read MoreChristopher Winslett
Christopher Winslett
JSON is everywhere, even in your SQL database. Since Postgres added JSON support 10 years ago, we've seen widespread adoption. When JSON support was first released in 2012 the implementation was a fairly flat representation of the JSON data type. It had limited querying and indexing capabilities. Craig , who was active in early JSON discussions with Postgres contributors, admits "Postgres cheated" with these early implementations. As JSONB has been introduced and adopted, I think you can see...
Read MoreCraig Kerstiens
Craig Kerstiens
A question recently came up in the internal Crunchy Data slack channel: Does anyone actually use enums out in the wild? If you're unfamiliar with enums, they’re enumerated types, a static set of values in a database like days of the week or a shipping status. Enums are a powerful feature of Postgres that allows you to define a set of predefined values that can be assigned to a column. However, enums can have some limitations and drawbacks that make them less than ideal for certain scenarios. L...
Read MoreElizabeth Christensen
Elizabeth Christensen
Crunchy Data hosted the 4th annual PostGIS Day on November 17, 2022. PostGIS Day always comes a day after GIS Day which occurs annually on the 3rd Wednesday of November. We had speakers from 10 different countries and attendees from more than 70 countries. PostGIS is the most popular spatial relational database worldwide with: • An extensive catalog of spatial functions • Rich ecosystem of in-db extensions for routing, event management, external database linkage, point clouds, rasters and m...
Read MorePaul Ramsey
Paul Ramsey
One of my favourite (in an ironic sense) data formats is the "CSV in the CSV", a CSV file in which one or more of the column is itself structured as CSV. Putting CSV-formatted columns in your CSV file is a low tech approach to shipping a multi-table relational data structure in a single file. The file can be read by anything that can read CSV (which is everything?) and ships around the related data in a very readable form. But how can we interact with that extra data? If you want to try this blo...
Read MorePaul Ramsey
Paul Ramsey
Back when I first learned SQL, calculating percentages over a set of individual contributions was an ungainly business: • First calculate the denominator of the percentage, • Then join that denominator back to the original table to calculate the percentage. First calculate the denominator of the percentage, Then join that denominator back to the original table to calculate the percentage. This requires two passes of the table: once for the denominator and once for the percentage. For BI queries...
Read MoreMatt Hudson
Matt Hudson
The operator has to be in the top-3 SQL-specific operators that people learn, the close competitors are and . Thus, feels familiar when crafting SQL: you can use it with nested SQL statements, or create your own list. But, it does have limitations — what if you wanted to send a list of unknown length? You can’t really use SQL placeholders without also modifying the SQL statement. What if I told you there was a less ubiquitous operator that offered all of the power and less of the drawback...
Read More