SQL · Chapter 30 of 42
ALTER TABLE
ALTER TABLE changes an existing table's schema: add/drop columns, rename, change type, add constraints.
On big tables, altering columns can lock the table — check your DB's docs.
Example 1 (sql)
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
Output
Column added
Add a new column.
Example 2 (sql)
ALTER TABLE users
DROP COLUMN phone;
Output
Column dropped
Remove a column (irreversible!).
Key points
- ADD COLUMN adds a column.
- DROP COLUMN removes a column (data lost).
- RENAME COLUMN renames it.
- ALTER COLUMN changes type or nullability.
💡 Note: In production, prefer additive migrations (add nullable column, backfill, then enforce NOT NULL) over destructive ones.
Understanding ALTER TABLE
ALTER TABLE changes the definition of an existing table, rather than creating a separate replacement. A table’s definition includes column names, data types, defaults, and constraints. For example, a packing list might need a status column after an application gains a dispatch workflow. UPDATE changes values in existing rows; ALTER TABLE changes the structure and rules those rows must follow. Some structural changes also require PostgreSQL to inspect or rewrite stored data.
The basic pattern is ALTER TABLE table_name followed by an action. ADD COLUMN introduces a field; DROP COLUMN removes one; RENAME COLUMN changes its name. ALTER COLUMN selects an existing column for operations such as TYPE, SET DEFAULT, or SET NOT NULL. These actions are not interchangeable: renaming a text column to units does not turn its contents into integers. You must request the type change separately.
Existing rows matter whenever you tighten a definition. Adding a nullable column without a default gives existing rows NULL in that column. Setting a default afterward does not replace those NULLs: it supplies a value when a later insert omits the column or requests DEFAULT. Likewise, SET NOT NULL succeeds only when existing values satisfy the rule. A deliberate backfill with UPDATE often belongs between adding a column and enforcing its requirements.
Type changes have their own compatibility questions. PostgreSQL can perform some conversions automatically; others need a USING expression describing how each old value becomes a new value. Converting text to integer works only when the input represents an acceptable integer. Constraints also inspect meaning: a quantity can be a valid integer yet violate a CHECK requiring a positive number. Decide which values are legitimate before changing the schema.
Schema changes also affect availability and application compatibility. Many ALTER TABLE forms take strong locks, and some require scans or rewrites. Even a quick metadata change can wait behind another transaction. Renaming or removing a column can break queries that still use its old name. Prefer staged, additive changes when compatibility matters, but recognize that backfills also consume resources. A small successful exercise demonstrates syntax and semantics, not the operational cost of changing a large production table.
Practical walkthrough
Use PostgreSQL 18 and a SQL client connected to a database where you may create temporary tables. Run the example in one session, with an unused temporary-table name. No extensions or external data are required. The transaction creates a disposable temporary table and ends with ROLLBACK, undoing the exercise. Do not substitute a real application table: this script is intentionally isolated practice.
First, create two packing-list entries whose quantities are stored as text. This models an imperfect starting schema without depending on an existing database. The primary key distinguishes entries, and the initial NOT NULL requirement prevents missing quantities.
Next, add status as nullable text. At this point both existing rows have NULL status. Update them to ready because, for this exercise, both represent already prepared packages. That assumption matters: a real backfill needs a rule based on actual business meaning, not a convenient value chosen merely to satisfy a constraint.
Set queued as the default for subsequent inserts, then require status to be non-null. This ordering separates three decisions: what old rows mean, what omitted values on new rows should mean, and whether missing values are permitted.
Rename quantity_text to units, then convert its type using an explicit cast. Add a named positive-value CHECK after conversion. Insert one more entry without specifying status to demonstrate the default. Finally, select explicit columns with ORDER BY id so the displayed row sequence is deterministic. Read that result before ROLLBACK removes the exercise’s changes; command-status messages and table borders depend on your SQL client.
Worked example
BEGIN;
CREATE TEMPORARY TABLE alter_table_packing_demo (
id integer PRIMARY KEY,
quantity_text text NOT NULL
);
INSERT INTO alter_table_packing_demo (id, quantity_text)
VALUES (1, '2'), (2, '5');
ALTER TABLE alter_table_packing_demo
ADD COLUMN status text;
UPDATE alter_table_packing_demo
SET status = 'ready';
ALTER TABLE alter_table_packing_demo
ALTER COLUMN status SET DEFAULT 'queued';
ALTER TABLE alter_table_packing_demo
ALTER COLUMN status SET NOT NULL;
ALTER TABLE alter_table_packing_demo
RENAME COLUMN quantity_text TO units;
ALTER TABLE alter_table_packing_demo
ALTER COLUMN units TYPE integer USING units::integer;
ALTER TABLE alter_table_packing_demo
ADD CONSTRAINT packing_units_positive CHECK (units > 0);
INSERT INTO alter_table_packing_demo (id, units)
VALUES (3, 1);
SELECT id, units, status
FROM alter_table_packing_demo
ORDER BY id;
ROLLBACK;
Expected output
id | units | status
1 | 2 | ready
2 | 5 | ready
3 | 1 | queued
The output shows the SELECT result, excluding client-specific command messages. Entries 1 and 2 retain their identifiers and numerical quantities, but units now has integer type rather than text type. Their ready status comes from UPDATE, not from the default. Entry 3 receives queued because its INSERT omits status after the default is established. Renaming preserves the original quantity column’s NOT NULL requirement; the added CHECK separately rejects zero and negative quantities. All three rows satisfy both rules. ROLLBACK then undoes the transaction, including creation of the temporary table, so this demonstration leaves no table or inserted rows behind.
Common mistakes and how to avoid them
Expecting SET DEFAULT to repair existing NULL values.
Backfill existing rows explicitly before setting NOT NULL. A default applies to omitted values or DEFAULT requests; an explicit NULL does not invoke it.
Assuming USING makes every type conversion valid.
Inspect and clean incompatible values first. USING specifies the conversion expression, but text such as 'several' still cannot be cast to integer.
Treating DROP COLUMN as hiding a field.
It removes the column and its stored values. After commit, ordinary transaction rollback cannot restore them. Check dependencies and recovery options before removal; avoid casual CASCADE.
Equating a successful small test with a safe production migration.
Check the specific operation’s locking and rewrite behavior, test representative data, and coordinate application changes. Keep transactions short because locks can remain until transaction end.
Review questions
Why does the example use UPDATE as well as ALTER TABLE?
ALTER TABLE introduces status and defines its rules. UPDATE supplies meaningful values for existing rows, allowing the later NOT NULL requirement to succeed.
What happens if a new insert explicitly supplies NULL for status?
The insert fails the NOT NULL requirement. The queued default does not replace an explicitly supplied NULL; omit status or use DEFAULT to request it.
Does CHECK (units > 0) alone guarantee a positive, present quantity?
No. A CHECK accepts a condition that evaluates to unknown because of NULL. NOT NULL excludes missing values, while this CHECK excludes zero and negatives.
Is dropping a column always immediately irreversible in PostgreSQL?
No. Ordinary transactional ALTER TABLE changes can be rolled back before commit. Once a drop is committed, recovery requires another source, such as a suitable backup.
