Safely Alter Postgres Columns with USING

But first, Arrays

Postgres has this super useful data type called Array. Not to throw out an obscure term without defining it, an “array” is a sort of list of things 😉. Have you ever wanted to associate a row in your database with a list of things? Without the array type, you’d have to create a join table. This might be the best option in some cases, but if you just want to add a few items to each row, or if you want to do the same thing for multiple lists of items, Postgres arrays to the rescue!

At Echobind we recently had a situation where we needed to alter a column from a scalar (a enum field) to an array (each row could be classified under multiple “project types”). A simple query, right?

Well, we have a problem. This query will fail if any rows have a non-null value in . We could just set all the values to (we would also need to allow values in that column), but that would destroy our data. We could create a migration script, but that seems like a lot of work.

Finally, USING

Thankfully Postgres has us covered once again! It provides the USING clause.

The USING clause specifies how to compute the new column value from the old.

It’s basically a “function” that takes the old value in the column and generates a new value (spoiler: it does even more than that!). We can use in our original query to do something like this:

This tells Postgres to take the value in (before the is applied), put it into an array, and use that as the value for the column after the is applied. With this version of the query, we don’t destroy any data.

This tells Postgres to use the first value in the array (Postgres arrays start at one—I’m sure there are reasons, but still 🤷) as the new value. But as I hinted before, this will only be non-destructive if you haven’t added any more items to the array (i.e. it only has one value in each row). If it has more than one value, you’ll lose all of them except the first.

The rumors are true, there’s more

I know, how could it get better? Well, lay aside those low expectations and prepare to be astonished; is so much more powerful. When building your clause, you can use:

  1. Any of the columns in your table (not just the one being modified).
  2. Any Postgres function.
  3. Explicit casting.
  4. A fourth thing.

All the columns

doesn’t limit you to just the column you’re altering. You can use any of the columns in your table. Let’s say you have a table like this that tracks some metrics:

This was a bad decision; should not be human-readable text. It would be better if it was just the total number of visits and we could use it in code however we want:

With this, we’re altering to be the sum of the and columns.

And just to cover all our bases, we could roll this back with:

All the functions

Want to do some more complex manipulation? You can use Postgres functions in . Let’s say we’ve been storing a timestamp in the MySQL DATETIME format ( ). But then we come to learn that Postgres has its own timestamp data type (we’re always learning as developers; no shame in not knowing the best chili dog toppings at our first rodeo).

Postgres has a built-in function for converting strings to timestamps, , and we can use it in the clause:

This converts our DATETIME string value into a native timestamp, using the specified format.

And to stay on brand, we can roll it back with:

Boring, explicit casting

When you change the data type of a column with , Postgres will do its best to cast the current value to the new type. But sometimes it can’t (where is my ‘90s dream of an artificial intelligence utopia) and you need to explicitly define how you want Postgres to cast your value.

Let’s say we have some JSON data, but we’re storing it in a field. We want to convert that to a fancy field:

Without the clause, Postgres would complain:

column "specs" cannot be cast automatically to type json

But with the explicit cast it works just fine.

Postgres can cast JSON to TEXT on its own, so we can roll this back without (sad, I know). But just to include one more example, here’s what it would look like doing it explicitly:

Dare to dream, a fourth thing

I was really hoping this would be “You can even use subqueries 🤯”. But we dreamt too big. Even has its limits.

All good things

Are you still reading? It’s over. Go home. I am contractually obligated to say that in this article we covered the clause and how its magic can be employed to alter table columns in Postgres. We looked at so many shiny examples of using with columns and functions and casting and not (maybe someday, in the machine utopia) subqueries.

Resources

More about:

Cully Larson

Cully is a Lead Engineer at Echobind. Passionate about working with teams to solve software problems and helping clients release industry-leading products, Cully enjoys all facets of software development. When he's not at the keyboard, he enjoys cycling, backpacking, and making stuff.