How to Drop a Column in PostgreSQL

To drop a column in PostgreSQL, we use the ALTER TABLE command followed by DROP COLUMN.

Basic Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example: Dropping a Column

Let’s say you have a table employees with the following schema:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    birth_date DATE
);

To drop the birth_date column, use the following command:

ALTER TABLE employees
DROP COLUMN birth_date;

Verifying the Column Drop

After executing the command, you can verify the column has been dropped by checking the table schema:

\d employees

Expected output:

                                    Table "public.employees"
   Column    |         Type          | Collation | Nullable |              Default              
-------------+-----------------------+-----------+----------+-----------------------------------
 id          | integer               |           | not null | nextval('employees_id_seq'::regclass)
 first_name  | character varying(50) |           |          | 
 last_name   | character varying(50) |           |          | 
 email       | character varying(100)|           |          | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)

Why Drop a Column?

There are several reasons you might want to drop a column from a PostgreSQL table:

• Reducing Storage: Removing unused columns can save disk space.
• Improving Performance: Queries might perform better without unnecessary columns.
• Schema Evolution: Updating the database schema as application requirements change.

Dropping Multiple Columns

You can drop multiple columns in a single command. For instance, if you want to drop both the email and last_name columns:

ALTER TABLE employees
DROP COLUMN email,
DROP COLUMN last_name;

This command will drop both email and last_name columns from the employees table in a single operation.

Simplifying Column Dropping with Beekeeper Studio

Beekeeper Studio is a cross-platform SQL editor and database manager that can simplify SQL operations like dropping columns. With Beekeeper Studio, you can drop columns from your PostgreSQL table using a user-friendly interface without the need to write SQL commands. With simplified database management, clean UI/UX, and a considerable reduction in the risk of data-altering mistakes, Beekeeper Studio is
a great option for your PostgreSQL operations.

Considerations and Best Practices

• Backups: Dropping a column will permanently remove the column and all the data stored in it. So, ensure you have a backup or you’re certain that the data is no longer required.
• Dependent Objects: When dropping columns that other database objects reference, like views or stored procedures, ensure you adjust those objects as necessary to prevent errors. If the column is part of a foreign key relationship with another table, you’ll need to drop that constraint first.

Conclusion

Dropping a column in PostgreSQL is a simple and powerful operation that can help to modify and optimize your database schema. Keep the considerations and best practices in mind when removing unnecessary columns from your tables.

Other articles you may enjoy:

• How to Drop Constraints in PostgreSQL
• How to List Databases using psql