How to Drop Multiple Columns in PostgreSQL (With Example)

Often in PostgreSQL you may want to drop multiple columns from a table at once.

You can use the DROP statement with the following syntax to do so:

ALTER TABLE athletes
DROP team, DROP assists;

This particular example will drop the team and assists columns from the athletes table.

Note: In this example we used the DROP statement twice to drop two columns, but you can use the DROP statement as many times as you would like to drop even more columns.

The following example shows how to use this syntax in practice.

Example: How to Drop Multiple Columns in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER,
  team TEXT,
  points INTEGER,
  assists INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     22 |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Mavs    |     31 |      14
         6 | Mavs    |     16 |       3
         7 | Hawks   |     10 |       5
         8 | Celtics |     14 |      13
         9 | Hawks   |     20 |       8
        10 | Hawks   |     26 |       3

Suppose that we would like to drop both the team and assists columns from the table.

We can use the following syntax to do so:

-- drop team and assists columns from table
ALTER TABLE athletes
DROP team, DROP assists;

-- view updated table
SELECT * FROM athletes;

 athleteid | points 
-----------+--------
         1 |     22
         2 |     30
         3 |     12
         4 |     25
         5 |     31
         6 |     16
         7 |     10
         8 |     14
         9 |     20
        10 |     26

Notice that both the team and assists columns have been dropped from the athletes table.

You can use similar syntax to drop multiple columns at once in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
PostgreSQL: How to Count True and False Values