Grant Fritchey

19 September 2025

Introducing PostgreSQL Static Data in Flyway

One kind of data in most relational databases is what we call static data. This is also referred to as lookup data, code data, domain data or even list data. Whatever you like to call it, it’s usually smaller data sets consisting of data that never changes, or changes very slowly. One example might be Canadian postal codes. Another example, and one I’m going to use, is the amateur radio band definitions within a given country. The radio spectrum is fixed and barring changes to the law, the 2 meter band in the US is always going to be 144-148mhz.

Unlike transactional data within a database, static data is usually loaded as part of a deployment. It’s there at the start of the databases lifetime. Transactional data can come and go, but the lookup information supporting your transactional tables is pretty permanent. By it’s nature, these data sets are also usually pretty small. Because of all this, it’s natural to want to just put this stuff in source control and deploy it right after the schema gets deployed.

Redgate has had this working with SQL Server for, well, decades now. I’m pleased as can be to let you know that it also works with PostgreSQL through our Flyway tool. Let’s take a look.

Defining Static Data

For our purposes, I’m going to create a new project. I start with the connection settings:

With all that defined, when I click on “Continue” I’m offered an option with regards to my static data:

Since the whole purpose here is to show of static data, of course, we’re going to switch the selection to “Choose data to track”. That then opens up some additional choices. As you can see below, I’ve already selected the radio.countries table. I can then type into the box to search for radio.bands, or, since the list of tables for this database is so short, I could easily scroll down and select it:

When I’m done selecting the tables I’m interested in, I can click on “+ Track selected tables.” That will define the tables I’m interested in and give me the opportunity, if I choose, to decide which columns to store in source control. It’s entirely possible that you may need to only get some of the columns. In my case I’m tracking them all:

I’m using a migrations approach on my database (I just prefer it to state these days), so I can go and look at the generated migration file for an initial deployment of my database:

There you go. A nice simple insert statement for my radio.bands table.

But what happens when I add data to one of my tables? Well, I’m going to add a band for Germany that I missed:

Back in Flyway Desktop, I can refresh the development database. I’ll now see that there’s an outstanding change. Further, it will show me exactly what that change is:

And that’s about it. From there I’ll save this into my existing schema. I can then generate a new migration script to get the row added through a deployment process. Everything is nice and simple.

Now, I used the GUI because it’s just a handy way to describe the process. However, you can edit the TOML file to add tables you want to track through static data:

And you can always take a look at the JSON that stores the static data:

And of course, the Flyway commands are all available for doing the deployments and everything, just as before.

I hope you find this new functionality as useful as I do.