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:
ALTER TABLE contact
ADD username VARCHAR(50);
If we wanted to add multiple columns, we would separate each new column with a comma. With the same example table above, we could add two more columns:
ALTER TABLE contact
ADD password VARCHAR(50),
ADD email VARCHAR(50);
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.
