How to remove a column in PostgreSQL table

Introduction

The DROP COLUMN clause is used to remove one or more columns from a PostgreSQL table. This is one of the more commonly used and most useful database operations. The drop-column syntax is part of the ALTER TABLE statement and must be used whenever making a change to a table. This means the DROP COLUMN clause must be used in conjunction with the ALTER TABLE statement. This tutorial will provide explanations and examples for how to remove a column from a PostgreSQL table using the ‘DROP COLUMN’ clause with the ALTER TABLE statement.

Prerequisites

• PostgreSQL must be properly installed, configured and working properly on the local system with the server running in the background.

• A solid basic understanding of PostgreSQL is required in order to be able to follow the procedures laid out in this tutorial on how to remove a column from a PostgreSQL table.

PostgreSQL DROP Column in a table

Following is the basic form of the ‘DROP COLUMN’ command syntax:

Here is a breakdown of the above ‘DROP COLUMN’ syntax:

• First, the name of the designated table that the column will be removed from is provided immediately after the ALTER TABLE statement.

• Second, the name of the designated column field that will be removed from the table is inserted immediately after the DROP statement.

Use PostgreSQL to remove column example

For this example, create a table named persons consisting of two columns. One column is named person_id and the other column is called person_name, as shown here:

With the new table created, the drop column statement will be used to remove the person_gender column.

Alter table to drop a column in PostgreSQL example

Following is the syntax for removing a single column from a table:

After executing the above statement, PostgreSQL will display an ALTER TABLE prompt.

Now verify the operation was successful by executing the \d command followed by the name of the table. The results should resemble the following:

Note that the ‘person_gender’ column was removed from the above results.

Multiple columns can also be removed from the “persons” table by appending a comma (‘) to separate each of the columns that are being removed. The REMOVE COLUMN statement is then repeated, again specifying the field(s) being removed from the table. This process can be repeated as desired.

The following example shows how to remove both the person_age and person_lastname columns from the table:

PostgreSQL will again provide the ALTER TABLE prompt after executing the above query.

Now executing the ‘d’ command again should produce the following results:

Conclusion

This tutorial provided explanations and examples for how to remove a column from a PostgreSQL table. The tutorial specifically provided the basic form of the DROP COLUMN syntax and a breakdown of the command. The article then provided working examples for using PostgreSQL to remove a single column from a table and explained how to use the DROP COLUMN command to remove multiple columns from a table at the same time. Remember that a comma must be used to separate each of the columns that are being removed when removing multiple columns with just a single operation.