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:
1 2 | ALTER TABLE TABLE_NAME DROP column_name; |
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:
1 2 3 4 5 6 7 | CREATE TABLE persons( person_id INT PRIMARY KEY NOT NULL, person_name VARCHAR NOT NULL, person_lastname VARCHAR NOT NULL, person_age INT, person_gender VARCHAR ); |
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:
1 2 | ALTER TABLE persons DROP COLUMN person_gender; |
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:
1 2 3 4 5 6 7 8 9 | Table "public.persons" Column | Type | Collation | Nullable | Default -----------------+-------------------+-----------+----------+--------- person_id | integer | | not null | person_name | character varying | | not null | person_lastname | character varying | | not null | person_age | integer | | | Indexes: "persons_pkey" PRIMARY KEY, btree (person_id) |
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:
1 2 3 | ALTER TABLE persons DROP COLUMN person_age, DROP COLUMN person_lastname; |
PostgreSQL will again provide the ALTER TABLE
prompt after executing the above query.
Now executing the ‘d’ command again should produce the following results:
1 2 3 4 5 6 7 | Table "public.persons" Column | Type | Collation | Nullable | Default -------------+-------------------+-----------+----------+--------- person_id | integer | | not null | person_name | character varying | | not null | Indexes: "persons_pkey" PRIMARY KEY, btree (person_id) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started