Lesson 10 of 15 · 22 min
Changing a table with ALTER TABLE
By the end of this lesson you can change an existing table's columns, types, defaults and constraints with ALTER TABLE, and prepare existing data so a new rule can be added.
ALTER TABLE changes the definition of a table that already exists, keeping its rows. Each change is an action, and several actions can be combined in one statement, separated by commas. The ones you'll use most:
| Action | Example |
|---|---|
| add a column | ADD COLUMN phone text |
| remove a column | DROP COLUMN phone |
| rename a column | RENAME COLUMN city TO delivery_city |
| rename the table | RENAME TO clients |
| change a type | ALTER COLUMN stock TYPE bigint |
| set or remove a default | ALTER COLUMN status SET DEFAULT 'new' / DROP DEFAULT |
| require or allow NULL | ALTER COLUMN city SET NOT NULL / DROP NOT NULL |
| add or remove a constraint | ADD CONSTRAINT name CHECK (…) / DROP CONSTRAINT name |
Adding a column to a table with rows
A new column needs a value in every existing row. Without a DEFAULT, existing rows get NULL, which is why ADD COLUMN x integer NOT NULL fails on a non-empty table: the existing rows would violate the rule immediately. With a DEFAULT, PostgreSQL fills every existing row with it, so ADD COLUMN discontinued boolean NOT NULL DEFAULT false works in one step (and since PostgreSQL 11 it is instant even on huge tables, because a constant default is stored once in the catalog rather than written into every row).
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons