Modifying Tables

The Task Table

Remember that our task table looks like this at the moment:

create type status as enum ('todo', 'inprogress', 'done');

create table task (
    id serial primary key,
    title text not null unique,
    description text not null,
    status status,
    duration integer check (duration > 0),
    created_at timestamp default current_timestamp not null
);

Adding and Dropping Columns

To add a column, we can use the alter table ... add column statement.

For example, here is how we can add a priority column that has the text data type:

alter table task
add column priority text;

When adding a column, we can also specify constraints on it.

For example, here is how we can add a priority column with a check constraint:

alter table task
add column priority text
check (priority in ('low', 'medium', 'high'));

You can use the alter table ... drop column statement to remove a column.

For example, here is how we can drop the priority column again:

alter table task
drop column priority;

Adding and Dropping Constraints

You can add a new constraint to a column using the alter table ... add constraint statement.

For example, here is how we can add a check constraint to the duration column:

alter table task
add constraint check_duration_max check (duration <= 600);

You can drop an existing constraint from a column using the alter table ... drop constraint statement.

Let's undo the addition of the check constraint:

alter table task
drop constraint check_duration_max;

Renaming Columns and Tables

You can rename a column using the alter table ... rename column statement.

For example, here is how you can rename the column description to details:

alter table task
rename column description to details;

You can rename an entire table using the alter table rename ... statement.

For example, here is how you can rename the task table to task_list:

alter table task
rename to task_list;

Removing Tables

You can remove an entire table using the drop table statement.

For example, here is how you can remove the task table completely:

drop table task;

It hopefully goes without saying that you should be very careful when removing an entire table, as you will also remove all the data it currently stores.