Online College Courses for Credit

ALTER TABLE to Change Columns: Add/Drop

ALTER TABLE to Change Columns: Add/Drop

Author: Sophia Tutorial

Identify a correctly constructed ALTER TABLE statement to add and drop columns from a table.

See More

what's covered
This tutorial explores using the ALTER TABLE statement to add and drop columns in a table in two parts:
  1. Adding Columns
  2. Dropping Columns

1. Adding Columns

Once we have our tables created, we may need to make changes by adding or dropping a column. Ideally, we would have the tables correctly created from the start, but given business process changes we may need to make changes to our tables. Any changes to a table will use the ALTER TABLE statement. There are three options available: ADD, MODIFY, and DROP. We will focus on the ADD and DROP options here.

The basic syntax to add a column to a table looks like the following:

ALTER TABLE <tablename>
ADD <columnname> <datatype>;

Notice that the structure of the command after the ADD is quite similar to what we would see in a CREATE TABLE command.

If we had the following table created:

CREATE TABLE contact( contact_id SERIAL PRIMARY KEY );

We could ALTER the table to add in the username like this:

ADD username VARCHAR(50);

If we wanted to add multiple columns, we would separate each new column with a comma. Using the same example table above, we could add two more columns:

ADD password VARCHAR(50),
ADD email VARCHAR(50);

2. Dropping Columns

Some databases will not allow you to remove a column unless the column does not contain any values. Otherwise, dropping a column could delete important data that may be used by other tables. PostgreSQL, however, will allow you to drop a column even if there is data in the column, so you will want to be careful when dropping a column.

To remove a column, we would use a DROP command:

ALTER TABLE <tablename>
DROP <columnname>;

Note that we do not have to include the data type. To remove the username column from the contact table, we would do the following:

DROP username;

To remove two columns at once:

DROP password,
DROP email;

You could also mix the ADD and DROP options together in a single statement, although it is best practice to keep them in separate commands.

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

The ALTER TABLE command can be used to ADD and DROP columns from existing tables.

Source: Authored by Vincent Tran