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);
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.
Source: Authored by Vincent Tran