PostgreSQL DROP COLUMN: How to Remove Columns from a Table

In this article, you will learn how to delete one or more columns from an existing table using the PostgreSQL ALTER TABLE ... DROP COLUMN statement.

Occasionally, you may need to drop one or more columns from an existing table for the following reasons:

• The column is redundant or no longer needed by application business logic.
• The data type of the column must be changed in a way that requires recreating the column.
• The column has been superseded by a new normalized table or calculated value.

Suppose you have a users table storing attributes like username, email, and password. For security, you migrate passwords to an isolated credentials table, and then need to remove the obsolete password column from the main users table.

PostgreSQL provides the ALTER TABLE statement to modify existing table definitions. To delete one or more columns from a table, use the ALTER TABLE ... DROP COLUMN statement.

PostgreSQL DROP COLUMN Syntax

To drop one or more columns from a table, use the ALTER TABLE ... DROP COLUMN statement:

ALTER TABLE table_name
DROP [COLUMN] [IF EXISTS] column_name [RESTRICT | CASCADE]
[, DROP [COLUMN] ...];

Explanation:

• table_name is the name of the table from which you want to drop the column.
• DROP [COLUMN] column_name specifies the column to remove. The COLUMN keyword is optional. To drop multiple columns in a single statement, separate multiple DROP COLUMN clauses with commas.
• IF EXISTS is optional. It prevents PostgreSQL from raising an error if the specified column does not exist.
• column_name is the name of the column to delete.
• CASCADE | RESTRICT controls how PostgreSQL handles objects that depend on the column (such as foreign keys, views, triggers, or stored procedures):
  • CASCADE drops the specified column and automatically cascades the deletion to any dependent objects (e.g. constraints, views).
  • RESTRICT refuses to drop the column and raises an error if any other objects depend on it. This is the default behavior.

When you drop a column from a table, PostgreSQL automatically removes all indexes and constraints that depend solely on the dropped column.

PostgreSQL DROP COLUMN Example

This example demonstrates how to drop columns and handle foreign key dependencies in PostgreSQL.

First, create two tables named users and user_hobbies in the database. The users table stores user profile data, and user_hobbies references users(user_id) with a foreign key constraint:

CREATE TABLE users (
  user_id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(45) NOT NULL,
  age INTEGER,
  locked BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL
);

Now create the user_hobbies table:

CREATE TABLE user_hobbies (
  hobby_id SERIAL NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

Use the \d command in psql to inspect the structure of user_hobbies:

\d user_hobbies

                                          Table "public.user_hobbies"
  Column   |            Type             | Collation | Nullable |                    Default
-----------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id  | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id   | integer                     |           | not null |
 hobby     | character varying(45)       |           | not null |
 created_at| timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
    "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

Notice that the foreign key constraint fk_user on user_hobbies references the user_id column in users.

Attempting to Drop a Referenced Column

The following statement attempts to delete the user_id column from the users table:

ALTER TABLE users
DROP COLUMN user_id;

Output:

ERROR:  cannot drop column user_id of table users because other objects depend on it
DETAIL:  constraint fk_user on table user_hobbies depends on column user_id of table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Because the foreign key fk_user depends on users(user_id), PostgreSQL prevents accidental schema breakage and raises an error under the default RESTRICT behavior.

Forcing Removal with CASCADE

To drop the column along with dependent constraints and objects, add the CASCADE option:

ALTER TABLE users
DROP COLUMN user_id CASCADE;

Output:

NOTICE:  drop cascades to constraint fk_user on table user_hobbies
ALTER TABLE

PostgreSQL issues a notice explaining that the deletion cascaded to fk_user, and completes the alteration.

Verify that user_id has been removed from users:

\d users

                          Table "public.users"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 name       | character varying(45)       |           | not null |
 age        | integer                     |           |          |
 locked     | boolean                     |           | not null | false
 created_at | timestamp without time zone |           | not null |

Next, verify that the referencing foreign key constraint has also been removed from user_hobbies:

\d user_hobbies

                                          Table "public.user_hobbies"
  Column   |            Type             | Collation | Nullable |                    Default
-----------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id  | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id   | integer                     |           | not null |
 hobby     | character varying(45)       |           | not null |
 created_at| timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)

Conclusion

The PostgreSQL ALTER TABLE ... DROP COLUMN statement provides a clean way to remove obsolete columns from your tables. Remember that dropping a column will also remove associated single-column indexes and constraints. When foreign keys or views depend on the column, use CASCADE cautiously to avoid unintentionally removing downstream schema elements.

For further table management techniques, explore renaming tables, renaming columns, adding columns, and creating tables.