Recently I came across a scenario where we had to alter a column type of a column from char to a decimal.

For some reason, previous developer had used a character type for a storing money.

We had two options either to replace that column with a new column with same name and a decimal data type or to alter the table. Fortunately we could replace that column as we’re just in dev environment and could afford loosing on data but if you can’t, there is an easier way and the reason why I’m writing this is because there isn’t much documentation on using Postgres’ USING clause in alembic and I had to scour the internet to find something that would work.

The thing is, when you add or remove columns on your database using SQL alchemy, it uses alembic as the engine to write the migrations files but alembic doesn’t recognize altering data types for a column, or at least doesn’t at the time of writing this article. So let’s get our hands dirty.

Check out this explanation of why we should use numeric data type to store monetary value instead of float or integer.

Let’s say this is your models.py file and you want to change the amount column from String to Numeric.

You would simply change amount = db.Column(db.Numeric) and then rum migration

Post running migrations, this is what your migrations file would look like.

When you run the upgrade command, it will give you the following error:

ERROR: column "amount" cannot be cast automatically to type numeric HINT: You might need to specify "USING amount::numeric".

To fix this, as the “HINT” suggests, you need to Postgres to use the USING clause.

Of course you can directly run a Query and fix

ALTER TABLE transactions
ALTER COLUMN amount TYPE NUMERIC;

But if you want to preserver the migration file and be able to duplicate it on various environments, it’s better to alter migrations file.

Just change alter migrations file to include the following bit of code.

postgresql_using=’amount::numeric’,

You can obviously change the type to accommodate to your needs, this is just to understand syntax of the alembic engine.