How to Change a Column Datatype Without Affecting Existing Users Postgresql

Source: Dev.to

Imagine you’re building a dating app. You’ve hit a major milestone: one million users. Congratulations!

In the early days you kept things simple. To track whether a user had children you used a single column called kids that stored a string, e.g.:

const updatePreferencesSchema = Joi.object({
  kids: Joi.string().valid(
    "Yes- they live with me",
    "Yes- they don't live with me",
    "no"
  ).optional()
});

Later the product team wants richer data: they need to know if a user has kids, whether those kids live at home, and the user’s desire for kids. The new schema looks like this:

const updatePreferencesSchema = Joi.object({
  kids: Joi.object({
    hasKids: Joi.boolean().required(),
    liveAtHome: Joi.boolean().required(),
    wantsKids: Joi.string().valid("Yes", "No", "Not Decided")
  }).optional()
});

Running a simple ALTER TABLE and deploying the new code will break existing rows that still contain the old string values. The application will throw a TypeError when it tries to read user.kids.hasKids from a row that still stores "no".

To handle the in‑between state you can use the Expand and Contract Pattern.

Phase 1: The Expand (The Safety Bridge)

Add a new column that will hold the data in the new format while keeping the old column untouched.

-- Step 1: Add the new column (defaulting to an empty object)
ALTER TABLE users ADD COLUMN kids_v2 JSONB DEFAULT '{}';

Dual‑Write Logic

Update the application to write to both columns. This ensures that new data is compatible with the old code (which still reads kids) and the new code (which reads kids_v2).

const oldValue = req.body.kids; // e.g., "yes- they live with me."

const updateData = {
  kids: oldValue,
  kids_v2: {
    hasKids: oldValue.toLowerCase().includes("yes"),
    liveAtHome: oldValue === "yes- they live with me",
    wantsKids: "Not Decided" // default for the new requirement
  }
};

Phase 2: The Migration (Backfilling the Past)

You now have a million rows where kids_v2 is just {}. Updating them all at once would lock the table, so process the data in batches.

Safe Batch Update Script (Node.js)

const { Pool } = require('pg');
const pool = new Pool();

async function backfillKidsData() {
  const batchSize = 5000;
  let hasMore = true;

  while (hasMore) {
    const res = await pool.query(`
      UPDATE users
      SET kids_v2 = jsonb_build_object(
        'hasKids', CASE WHEN kids ILIKE 'yes%' THEN true ELSE false END,
        'liveAtHome', CASE WHEN kids = 'yes- they live with me' THEN true ELSE false END,
        'wantsKids', 'Not Decided'
      )
      WHERE id IN (
        SELECT id FROM users
        WHERE kids_v2 = '{}' AND kids IS NOT NULL
        LIMIT ${batchSize}
      )
      RETURNING id;
    `);

    if (res.rowCount === 0) hasMore = false;
    console.log(`Migrated ${res.rowCount} rows...`);

    // Give the DB and event loop a short breather
    await new Promise(r => setTimeout(r, 100));
  }
}

Run this script until it reports that no more rows need updating.

Phase 3: The Contract (The Clean Cut)

Once every row contains a valid object in kids_v2, you have achieved schema parity.

1. Switch the API – update the code to read only from kids_v2.
2. Monitor – watch logs for any “undefined property” errors for a few days.
3. Delete – drop the old column and remove the dual‑write logic.

-- Final cleanup
ALTER TABLE users DROP COLUMN kids;

Conclusion

The Expand and Contract pattern lets you evolve a column’s datatype without downtime or data‑integrity issues. By expanding the schema, safely migrating data in batches, and then contracting (cleaning up), you avoid the gap that would otherwise cause runtime errors.