Altering schema

Schemas are never finished. New features need new columns, old ones become obsolete, and rules tighten as you learn what the data really looks like. The ALTER TABLE statement changes the shape of an existing table in place, keeping the rows you already have. Like CREATE TABLE, it is DDL and is usually applied through a migration step so the change is versioned and repeatable.

We will continue evolving the products table from earlier in the module.

Adding and dropping columns

ADD COLUMN appends a new column to every existing row. If you give it no default, existing rows get null in the new column. DROP COLUMN removes a column and its data for good.

ALTER TABLE products ADD COLUMN description text;

ALTER TABLE products DROP COLUMN description;

You can combine several changes in one statement by separating them with commas, which keeps related edits in a single, atomic step.

ALTER TABLE products
  ADD COLUMN sku text,
  ADD COLUMN in_stock boolean NOT NULL DEFAULT true;

In pgAdmin: after altering a table, refresh its Columns node in the browser tree to see the change, or use the table’s Properties dialog to add and remove columns through a form.

Changing a column’s type

ALTER COLUMN ... TYPE converts a column to a new type. PostgreSQL can do many conversions automatically, but when the change is not obvious you supply a USING expression that tells it how to transform each existing value.

ALTER TABLE products
  ALTER COLUMN sku TYPE varchar(32);

ALTER TABLE products
  ALTER COLUMN price TYPE numeric(12, 2) USING price::numeric(12, 2);

The conversion runs over every row, so on a large table this can take a while and hold a lock. The final lesson section covers how to keep that safe.

Adding and dropping constraints

You can attach a constraint to a table after it exists, or drop one by name. Naming constraints when you create them, as the previous lesson recommended, is what makes dropping them clean.

ALTER TABLE products
  ADD CONSTRAINT products_price_positive CHECK (price > 0);

ALTER TABLE products
  DROP CONSTRAINT products_price_positive;

Adding a constraint scans existing rows to confirm they all satisfy it; if any row fails, the whole statement is rejected and nothing changes.

Renaming

RENAME changes the name of a table or a single column without touching the data. It is instant, but anything that referenced the old name — views, application queries, saved reports — must be updated too.

ALTER TABLE products RENAME COLUMN sku TO product_code;

ALTER TABLE products RENAME TO catalog_items;

Safe migrations: the NOT NULL trap

The most common migration accident is adding a NOT NULL column to a table that already has rows. Those existing rows have no value for the new column, so the constraint is immediately violated and the statement fails. There are two safe paths: give the column a DEFAULT so every existing row is filled at once, or add the column nullable, backfill the values, then tighten it to NOT NULL.

-- Path 1: a default fills every existing row in one step
ALTER TABLE products
  ADD COLUMN currency text NOT NULL DEFAULT 'USD';

-- Path 2: add nullable, backfill, then enforce
ALTER TABLE products ADD COLUMN region text;
UPDATE products SET region = 'global' WHERE region IS NULL;
ALTER TABLE products ALTER COLUMN region SET NOT NULL;

Path two is the standard choice when there is no single sensible default, because it lets you compute each row’s value before locking the column down.

Running a migration in a transaction

Most ALTER TABLE statements in PostgreSQL are transactional, so you can wrap a multi-step migration in a transaction and have it apply all-or-nothing. If any step fails, a ROLLBACK leaves the table exactly as it was.

BEGIN;
ALTER TABLE products ADD COLUMN region text;
UPDATE products SET region = 'global' WHERE region IS NULL;
ALTER TABLE products ALTER COLUMN region SET NOT NULL;
COMMIT;

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('ALTER TABLE products ADD COLUMN region text');
  await client.query("UPDATE products SET region = 'global' WHERE region IS NULL");
  await client.query('ALTER TABLE products ALTER COLUMN region SET NOT NULL');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

with conn.transaction():
    cur.execute("ALTER TABLE products ADD COLUMN region text")
    cur.execute("UPDATE products SET region = 'global' WHERE region IS NULL")
    cur.execute("ALTER TABLE products ALTER COLUMN region SET NOT NULL")
# commits if the block succeeds, rolls back on exception

tx, err := conn.Begin(ctx)
if err != nil {
    return err
}
steps := []string{
    "ALTER TABLE products ADD COLUMN region text",
    "UPDATE products SET region = 'global' WHERE region IS NULL",
    "ALTER TABLE products ALTER COLUMN region SET NOT NULL",
}
for _, s := range steps {
    if _, err := tx.Exec(ctx, s); err != nil {
        tx.Rollback(ctx)
        return err
    }
}
err = tx.Commit(ctx)

let mut tx = pool.begin().await?;
sqlx::query("ALTER TABLE products ADD COLUMN region text")
    .execute(&mut *tx)
    .await?;
sqlx::query("UPDATE products SET region = 'global' WHERE region IS NULL")
    .execute(&mut *tx)
    .await?;
sqlx::query("ALTER TABLE products ALTER COLUMN region SET NOT NULL")
    .execute(&mut *tx)
    .await?;
tx.commit().await?;

Because the three steps run inside one transaction, the table is never seen by other sessions in a half-migrated state.

Tips / gotchas

ALTER TABLE takes a lock on the table. Many forms take a strong lock that briefly blocks reads and writes, so on a busy production table prefer short, targeted statements and run them during quiet periods.
Adding a NOT NULL column with a constant DEFAULT is fast in modern PostgreSQL because it does not rewrite every row, but changing a column’s type still scans and rewrites the table.
Wrap multi-step migrations in a transaction so a failure halfway through rolls back cleanly and never leaves the schema partly changed.
Adding a constraint validates every existing row. On a large table this scan can be slow; investigate adding the constraint as NOT VALID first and validating it separately when you need to minimize lock time.
DROP COLUMN and DROP CONSTRAINT are irreversible once committed. Confirm nothing depends on what you are removing, and lean on a transaction you can roll back while you check.