Transcript

In this example, we're going to be dealing with a dummy database that has a table called users, with the columns name and employee number.

We can select from this table and see that we already have data: 

select * from users;

ADD COLUMN

To add a new column to our table, we use the "alter table" command, with the table we are changing. We then specify what action we are going to do to the table, which in our case is "add column". Finally, we specify our desired column name and type, and any constraints we may want on it.

Here, I'm adding a new column called email, with a unique constraint.

alter table users add column email varchar unique;

Now we can inspect our table and see our new column and constraint.

\d users

DROP COLUMN

To remove a column from our table, we use the same "alter table" command as before, once again passing "users" as our table name. This time however, our action is going to be "drop column", and we're going to get rid of the email column that we just created.

alter table users drop column email;

The drop column command will automatically remove any constraints or indexes related to the column we are dropping. So if we're getting rid of our email column, we should see that the unique constraint for that column is also removed.

We can verify this by inspecting our table again.

\d users

Thanks for watching!

Setup

create table users (
  name varchar not null,
  employee_number varchar not null unique
);

insert into users
values ('Sam', 'EM1234'),
('Emily', 'EM2345');