Schinckel.net

Helped out a person in #django IRC some time ago, and learned something new about Postgres.

I’ve had data type migrations in the past (where you change a database column in some way, and need to alter the data that is already in that column), however I’ve created a function to do so.

It turns out you can just write an expression, and that works too:

  ALTER TABLE defect_defect
 ALTER COLUMN risk_rating
          SET DATA TYPE INTEGER
              USING CASE WHEN risk_rating = 'HIGH'   THEN 3
                         WHEN risk_rating = 'MEDIUM' THEN 2
                         WHEN risk_rating = 'LOW'    THEN 1;

Of course, this still would cause issues if you had code running from the old version (that expected the text values). However, it is nice to know.