SQL, how to update a table structure

Use ALTER TABLE to change an existing schema. Adding a nullable column is the simplest example:

ALTER TABLE people
ADD COLUMN born_year INTEGER;

Existing rows do not suddenly gain known birth years. Their new value is NULL:

id | name   | born_year
---+--------+----------
1  | Flavio | NULL
2  | Roger  | NULL

If the column must eventually be required, do not assume one statement is safe for a large live table. A compatible sequence is:

1. add the nullable column
2. deploy code that understands both schemas
3. backfill values in controlled batches
4. verify no NULL values remain
5. add the required constraint

This lets old and new application versions overlap during deployment.

Dropping a column destroys its stored values:

ALTER TABLE people
DROP COLUMN born_year;

Remove application reads and writes first. Keep the column through a compatibility window, then drop it in a later migration.

ALTER TABLE capabilities and locking behavior differ between database systems and versions. Some changes update metadata quickly. Others scan or rewrite the table and can block application work. Test the exact operation on representative data.

A down migration is not always a recovery plan. Recreating a dropped column does not restore its values. Back up important data and decide how to reverse the application deployment before changing production.

Your action is to add a nullable column to a practice table, inspect old and new rows, then write the verification query required before making it NOT NULL.