Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

  • 3 min read

    Postgres + Tailscale – A database you can trust with a simple VPN

    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 More
  • 7 min read

    How to Solve Advent of Code 2022 Using Postgres - Day 3

    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 More
  • 10 min read

    How to Solve Advent of Code 2022 Using Postgres - Day 2

    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 More
  • 4 min read

    How to Solve Advent of Code 2022 Using Postgres - Day 1

    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 More
  • Postgres support for JSON is 10 years old!

    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 More
  • Enums vs Check Constraints in Postgres

    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 More
  • 4 min read

    PostGIS Day 2022

    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 More
  • Postgres Strings to Arrays and Back Again

    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 More
  • Percentage Calculations Using Postgres Window Functions

    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 More
  • Postgres Query Boost: Using ANY Instead of IN

    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