Safely Casting Columns in Postgresql Without Losing Data

You know that moment when your “quick prototype” becomes a production system and suddenly your Postgres schema is working against you?

Early in one of my projects, we used PostgreSQL enums to keep things clean and tightly constrained. It made sense: enums for status columns, integers for money (in cents), lean schema, fast iteration.

But then the product grew. More dynamic logic, new values for status, higher transaction volumes. And that enum? It became a blocker. That int? It silently capped us at $21M.

Here’s the thing: PostgreSQL is powerful, but unless you’re careful with schema changes, you can wreck production with a single migration.

😵 The First Time I Lost Data

I remember the first time I tried changing a column type from enum to string. I figured:

ALTER TABLE transactions ALTER COLUMN status TYPE text;

Should work, right?

Wrong. PostgreSQL choked; it didn’t know how to automatically cast the enum. I lost all the data in that column. Thankfully, it was dev data, so all good and no panic, but I learnt a valuable lesson.

I needed to tell Postgres how to make the change.

🛠 USING : Your Type-Casting Lifesaver

PostgreSQL provides a way to explicitly define how to cast existing data when changing a column's type. That’s what USING is for.

If I had just done this:

ALTER TABLE transactions
ALTER COLUMN status TYPE text USING status::text;

It would’ve worked perfectly.

This line tells Postgres:

“Hey, when changing the type, convert the current status value (an enum) to a text value explicitly.”

Postgres knows how to cast enum to text ('active'::status_enum → 'active'::text) — and it does so safely.

The whole thing runs in a transaction, so if something breaks, nothing gets committed. No partial data loss.

💥 Real-World Pain: When int Isn’t Enough

Enums aren't the only trap.

I once worked on a transaction table that stored amounts in cents using the default integer type. Seemed fine… until someone processed more than $21,474,836.47.

Boom 💥 Postgres threw an overflow error.

We hit the max value for a 32-bit signed integer.

The fix?

ALTER TABLE transactions
ALTER COLUMN amount TYPE bigint USING amount::bigint;

With bigint, you get: $92 quadrillion+, which is more than enough for nearly any financial system, unless you're building the central ledger for a galactic empire. 😄

🧨 Don’t Do This

Avoid migrations like this:

ALTER TABLE orders ALTER COLUMN status TYPE text;

Without USING, Postgres tries to guess how to cast values. If it can’t, it errors out, or worse, you get partial, broken conversions.

Also, don’t go down the painful path of:

1. Creating a temporary column,

2. Copying all the values manually,

3. Dropping the original column,

4. Renaming the temp column.

You don’t need all that.

🤔 Sidebarrrrrrrrr; Should You Even Use Enums?

Here’s a rule of thumb:

Want the best of both worlds?

Use text + a CHECK constraint like:

CHECK (status IN ('active', 'pending', 'cancelled'))

You’ll still need a migration to modify the check later (drop + re-add), but it’s far less painful than modifying enums, which require global type changes.

💡 Final Takeaways

• Always use USING When changing a column’s data type, it’s safe, clear, and rollback-friendly.

• Test on a staging DB or local dump before running migrations in production.

• Don’t rely on Postgres to guess your intentions; be explicit.

• Avoid enum and int traps by designing for scale early.

• When in doubt, bigint is your friend 🙌

• And remember: sometimes the constraint isn’t worth the constraint.

---

If you've ever lost data trying to do a “simple type change,” you’re not alone. Hopefully, with this trick in your toolbox, you won’t have to learn it the hard way.

Got a horror story or a slick migration trick? Kindly share in comments. 👇