ALTER: Evolving a Live Schema

ALTER TABLE changes a table after it already has data. That is the normal life of a database: the first schema is a draft, and production asks you to evolve it without pretending nobody is using it.

This lesson shows the mechanics live — add, drop, rename, and change columns — then separates the safe lab feeling from the real production risk: locks, rewrites, and old code still expecting the old shape.

Add a column, then change the default

ALTER TABLE is DDL for changing a table definition — its main job is changing shape, not editing row data. Start with a tiny pickup table:

CREATE TABLE cafe_pickups (
  pickup_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_name text NOT NULL,
  order_total numeric(6,2) NOT NULL,
  quantity_text text NOT NULL,
  delivery_note text
);
 
INSERT INTO cafe_pickups (customer_name, order_total, quantity_text, delivery_note)
VALUES
  ('Ada', 8.50, '2', 'window seat'),
  ('Ken', 4.25, '1', 'extra napkins');
 
ALTER TABLE cafe_pickups
ADD COLUMN pickup_status text NOT NULL DEFAULT 'queued';
 
ALTER TABLE cafe_pickups
ALTER COLUMN pickup_status SET DEFAULT 'waiting';
 
INSERT INTO cafe_pickups (customer_name, order_total, quantity_text)
VALUES ('Priya', 6.75, '3');
 
SELECT pickup_id, customer_name, pickup_status
FROM cafe_pickups
ORDER BY pickup_id;

CREATE TABLE
INSERT 0 2
ALTER TABLE
ALTER TABLE
INSERT 0 1
 pickup_id | customer_name | pickup_status
-----------+---------------+---------------
         1 | Ada           | queued
         2 | Ken           | queued
         3 | Priya         | waiting
(3 rows)

The added column appears on existing rows as 'queued'. Then SET DEFAULT 'waiting' changes future inserts only, so Priya gets the new default while Ada and Ken keep the value they already had.

DROP DEFAULT is the reverse shape: it removes the future default; it does not erase stored values. That distinction matters every time you read an ALTER script: defaults are rules for the next write, not a bulk update to old rows.

Drop and rename columns

Dropping a column removes it from the table shape. Renaming a column changes the name existing SQL must use:

ALTER TABLE cafe_pickups
DROP COLUMN delivery_note;
 
ALTER TABLE cafe_pickups
RENAME COLUMN customer_name TO guest_name;
 
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'cafe_pickups'
ORDER BY ordinal_position;

ALTER TABLE
ALTER TABLE
  column_name  | data_type
---------------+-----------
 pickup_id     | bigint
 guest_name    | text
 order_total   | numeric
 quantity_text | text
 pickup_status | text
(5 rows)

Mechanically, the rename worked. Operationally, any app code, report, view, or import script still asking for customer_name is now broken. The table did not forget the data; your callers lost the old name.

The safer production rename is a two-phase rename, a staged change where old and new names coexist for a release window: add the new column, backfill it, deploy code that can read/write the new shape, then remove the old column in a later migration. That feels slower because it is honest about who else is connected.

Change a column type with USING

Changing a type is where ALTER starts feeling less like a label change and more like data work. PostgreSQL can only change a column type automatically when it knows how to convert every existing value. Otherwise, you give it a USING expression: USING tells PostgreSQL how to convert old values into the new type.

Here quantity_text stores digit text, so the conversion is explicit and safe:

ALTER TABLE cafe_pickups
ALTER COLUMN quantity_text TYPE integer
USING quantity_text::integer;
 
ALTER TABLE cafe_pickups
RENAME COLUMN quantity_text TO quantity;
 
SELECT pickup_id,
       quantity,
       pg_typeof(quantity) AS quantity_type
FROM cafe_pickups
ORDER BY pickup_id;

ALTER TABLE
ALTER TABLE
 pickup_id | quantity | quantity_type
-----------+----------+---------------
         1 |        2 | integer
         2 |        1 | integer
         3 |        3 | integer
(3 rows)

If one row had contained 'two', this conversion would fail. The right workflow is to inspect the dirty values first, clean them deliberately, then alter the type.

Modern PostgreSQL optimizes adding a column with a constant default, so that shape is not the same risk as computing a changing function value for every existing row. The exact lock and rewrite story depends on the subform and the table, which is why 18.4 gives migrations their own deeper lesson.

Add NOT NULL without painting yourself into a corner

The dangerous beginner move is one big statement on a table that already has rows:

ALTER TABLE cafe_pickups
ADD COLUMN contact_phone text NOT NULL;

ERROR:  column "contact_phone" of relation "cafe_pickups" contains null values

PostgreSQL is right to refuse it: existing rows would have no value. The safer rollout is expand → backfill → prove → enforce.

First expand with a nullable column:

ALTER TABLE cafe_pickups
ADD COLUMN contact_phone text;
 
SELECT pickup_id, guest_name
FROM cafe_pickups
WHERE contact_phone IS NULL
ORDER BY pickup_id;
 
UPDATE cafe_pickups
SET contact_phone = '555-0100'
WHERE contact_phone IS NULL;
 
ALTER TABLE cafe_pickups
ADD CONSTRAINT cafe_pickups_contact_phone_present
CHECK (contact_phone IS NOT NULL) NOT VALID;
 
ALTER TABLE cafe_pickups
VALIDATE CONSTRAINT cafe_pickups_contact_phone_present;
 
ALTER TABLE cafe_pickups
ALTER COLUMN contact_phone SET NOT NULL;
 
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'cafe_pickups'
  AND column_name = 'contact_phone';

ALTER TABLE
 pickup_id | guest_name
-----------+------------
         1 | Ada
         2 | Ken
         3 | Priya
(3 rows)
UPDATE 3
ALTER TABLE
ALTER TABLE
ALTER TABLE
  column_name  | is_nullable
---------------+-------------
 contact_phone | NO
(1 row)

That SELECT before the UPDATE is the write-safety ritual from Section 8: prove the target, write, then verify the new schema. NOT VALID adds a constraint without checking old rows immediately, and VALIDATE CONSTRAINT checks existing rows later. On a large table, the backfill step becomes many small batches instead of one broad update. The shape stays the same.

Migrations are versioned promises

A migration is a versioned script that changes schema in a controlled order. Instead of "I ran some ALTERs in a console," a team keeps files like 009_add_contact_phone.sql in source control, reviews them, runs them once per environment, and knows which version each database has reached.

The beginner migration checklist is short:

• One purpose per migration.
• Forward order is explicit.
• Backfills have rehearsals and verification queries.
• Renames use two phases when external code depends on the old name.
• Risky lock/rewrite behavior is called out before the script runs.

ALTER TABLE is how schemas grow up without starting over. Next, Section 10 steps back from commands and asks the design question first: what are the entities, attributes, and relationships before you type the DDL?