SQL ALTER TABLE

ALTER TABLE changes a table's definition in place. The rows stay where they are, which is what makes it different from rebuilding the table with CTAS: nothing is copied, and nothing is lost.

Columns

alter table customers add column segment varchar;

alter table customers rename column segment to customer_segment;

alter table customers drop column customer_segment;

A new column arrives as null on every existing row unless you give it a default, in which case existing rows get that value too.

alter table customers add column tier varchar default 'standard'

ADD COLUMN IF NOT EXISTS and DROP COLUMN IF EXISTS make a migration script safe to rerun, with the same caveat as everywhere else: IF NOT EXISTS stays quiet about the column it skipped, including when the existing one has the wrong type.

Renaming the table

create or replace table customers_staging as
select customer_id, first_name, email
from customers;

alter table customers_staging rename to customers_clean;

The old name stops resolving immediately. This is the cheap half of a swap-in: build the new table under a working name, check it, then rename.

Changing a type

alter table customers alter column customer_id type varchar

DuckDB converts the existing values, so this succeeds only if every one of them converts. A column of 'abc' retyped to integer fails with Conversion Error: Could not convert string 'abc' to INT32 and the table is left exactly as it was.

Check before you alter, rather than reading it from an error:

select customer_id
from customers_clean
where try_cast(customer_id as integer) is null
  and customer_id is not null

Defaults and constraints

alter table customers alter column tier set default 'standard';

alter table customers alter column tier drop default;

alter table customers alter column email set not null;

alter table customers add primary key (customer_id);

Adding a rule to a table that already holds data checks the data first. SET NOT NULL on a column containing nulls fails with NOT NULL constraint failed, and adding a primary key over duplicate values fails the same way. Clean the rows, then add the rule (see constraints).

What ALTER TABLE will not warn you about

This is the part worth internalizing. A table alteration succeeds on its own terms and says nothing about anything built on top of it.

Drop a column a view selects, and the drop succeeds. The view stays in the catalog and keeps looking healthy until somebody queries it, at which point it fails with Binder Error: Referenced column "drop_me" not found in FROM clause!. Renaming a column does the same thing, and renaming a table breaks every view built on the old name.

select view_name
from duckdb_views()
where not internal
  and lower(sql) like '%customers%'
order by view_name

It is crude: it matches the text of the stored definition, so it will catch a column name in a comment too. It is still faster than finding out later, and a false positive costs nothing.

Altering vs rebuilding

Sometimes the honest move is to rebuild rather than alter, especially when several things change at once.

Rebuilding drops constraints and defaults, since CTAS does not carry them. That is why a table that defends itself is better altered than replaced.

Doing it safely

• Look at the data first. try_cast and a count(*) where ... is null answer most "will this work" questions in a second.
• Snapshot anything you cannot recreate: create table customers_backup as select * from customers.
• Prefer adding to removing. A column nobody reads costs far less than a column somebody did.
• Deprecate in two steps (stop writing to a column, wait, then drop it) rather than discovering the reader after the fact.
• Query the dependent views afterwards. Every time.