How to remove a column in PostgreSQL table

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.