A schema changes. The shop needs a new column, a better name, a longer text. You could create the table again, but then its rows are gone. ALTER TABLE changes the table and keeps its rows.
Add a column
sql
ALTER TABLE suppliers ADD COLUMN email text;The rows that exist get NULL in the new column. With a DEFAULT, they get the default:
sql
ALTER TABLE suppliers ADD COLUMN active boolean NOT NULL DEFAULT true;suppliers
| id | name | country | active |
|---|---|---|---|
| 1 | Kraken Supply Co | CA | true |
| 2 | Golem Goods | US | true |
| 3 | Northern Plush | GB | true |
The other changes
| Change | Statement |
|---|---|
| remove a column and its values | ALTER TABLE suppliers DROP COLUMN email; |
| rename a column | ALTER TABLE suppliers RENAME COLUMN since TO partner_since; |
| rename the table | ALTER TABLE suppliers RENAME TO vendors; |
| change the type of a column | ALTER TABLE suppliers ALTER COLUMN name TYPE varchar(40); |
| make a column required | ALTER TABLE suppliers ALTER COLUMN country SET NOT NULL; |
| give a column a default | ALTER TABLE suppliers ALTER COLUMN country SET DEFAULT 'CA'; |
The rows must fit the change
PostgreSQL applies the change to every row. When one row does not fit, the statement fails and the table stays as it was:
text
ALTER TABLE suppliers ALTER COLUMN name TYPE varchar(10);
ERROR: value too long for type character varying(10)
ALTER TABLE suppliers ALTER COLUMN country SET NOT NULL;
ERROR: column "country" of relation "suppliers" contains null valuesSome type changes are not automatic. PostgreSQL does not change text to integer on its own, because a text such as CA is not a number. It changes integer to text, or varchar(20) to varchar(40), without a problem.
ADD COLUMN, DROP COLUMN, RENAME COLUMN and RENAME TO are the same in MySQL 8.