ALTER TABLE to Change Columns: Add/Drop

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:

ALTER TABLE contact
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:

ALTER TABLE contact
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:

ALTER TABLE contact
DROP username;

To remove two columns at once:

ALTER TABLE contact
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.