What can ALTER TABLE ... ALTER COLUMN change, and which of those changes touch existing rows?

## The family of actions Where `ADD COLUMN` and `DROP COLUMN` change which columns exist, `ALTER COLUMN` changes an existing column in place. The actions worth knowing: ```sql ALTER TABLE order_lines ALTER COLUMN qty SET DEFAULT 1; ALTER TABLE order_lines ALTER COLUMN qty DROP DEFAULT; ALTER TABLE order_lines ALTER COLUMN qty SET NOT NULL; -- PostgreSQL spelling ALTER TABLE order_lines ALTER COLUMN qty DROP NOT NULL; -- PostgreSQL spelling ALTER TABLE customers ALTER COLUMN email TYPE VARCHAR(320); -- PostgreSQL spelling ``` Sort them by whether they inspect the data, because that is what predicts whether the statement can fail on real content that a test database never had. ## SET/DROP DEFAULT — catalog only A default is a rule applied at insert time when the column is not supplied. Changing it changes that rule from now on and nothing else: ```sql -- 1,000 rows currently have qty = 5 ALTER TABLE order_lines ALTER COLUMN qty SET DEFAULT 0; -- all 1,000 rows still have qty = 5 ``` This asymmetry is the single most reliable question in this area, because the neighbouring statement behaves the other way: `ALTER TABLE t ADD COLUMN c INT DEFAULT 0` *does* give every existing row the value 0, since the column had no value before and the default is where one comes from. Adding a column with a default backfills; altering the default of an existing column does not. To change stored values you write an `UPDATE`, and it is a separate statement with separate consequences. `DROP DEFAULT` removes the rule; the column becomes one where an omitted value means NULL again (subject to its nullability). ## Nullability — validated against the data Tightening a column to `NOT NULL` requires that no row currently holds NULL; the engine checks and rejects the statement otherwise. Loosening it (`DROP NOT NULL`) can never fail on data, since every existing value remains legal. The spelling diverges sharply. PostgreSQL and several others use the dedicated `SET NOT NULL` / `DROP NOT NULL` actions. MySQL and SQL Server express nullability as part of a full column redefinition (see below), which is where the classic accident lives. ## SET DATA TYPE — conversion, and its limits ```sql ALTER TABLE customers ALTER COLUMN email TYPE VARCHAR(320); ``` Every stored value must convert to the new type. Widening conversions — `SMALLINT` to `INTEGER`, `VARCHAR(255)` to `VARCHAR(320)`, `NUMERIC(10,2)` to `NUMERIC(14,2)` — always succeed on valid data. Narrowing is a data question, not a schema question: shortening a `VARCHAR` fails if any value is longer, shrinking numeric precision fails or truncates depending on the engine, and changing `VARCHAR` to `INTEGER` fails on the first row that is not a number. When no implicit conversion exists, PostgreSQL lets you supply one with `USING`: ```sql ALTER TABLE events ALTER COLUMN happened_at TYPE TIMESTAMP USING happened_at::timestamp; ``` That clause is PostgreSQL-specific. The portable route for a hostile conversion is the same three-step dance used elsewhere in schema evolution: add a new column of the target type, populate it with an explicit `UPDATE` that encodes the conversion rules (including what to do with values that do not convert), then swap the names. ## The restate-the-definition trap MySQL and SQL Server do not have granular per-attribute actions for the common cases; the alter restates the whole column definition, and **whatever you omit is discarded**: ```sql -- MySQL: column was VARCHAR(255) NOT NULL DEFAULT '' ALTER TABLE customers MODIFY COLUMN email VARCHAR(320); -- the column is now nullable and has no default ``` The intent was "make it longer"; the effect was "make it longer, nullable, and defaultless". SQL Server's `ALTER TABLE ... ALTER COLUMN email varchar(320)` behaves the same way with respect to nullability — omit `NOT NULL` and the column becomes nullable. The habit that prevents this is to copy the column's current definition out of the catalog and change exactly the one token you meant to change: ```sql ALTER TABLE customers MODIFY COLUMN email VARCHAR(320) NOT NULL DEFAULT ''; ``` MySQL also offers `CHANGE COLUMN old_name new_name <full definition>`, which renames and redefines at once and carries the same all-or-nothing hazard. ## Summary | action | reads existing data? | can fail on data? | |---|---|---| | `SET DEFAULT` / `DROP DEFAULT` | no | no | | `DROP NOT NULL` | no | no | | `SET NOT NULL` | yes | yes — any NULL row | | `SET DATA TYPE` (widening) | yes | practically no | | `SET DATA TYPE` (narrowing) | yes | yes — first value that does not fit |