Postgres ALTER TABLE ... USING

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.