🧚 Listen! Beekeeper Studio is a fast, modern, and open source PostgreSQL GUI Download
July 25, 2024 By Matthew Rathbone

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

Before you keep reading, I just wanted to let you know about our open source PostgreSQL GUI, Beekeeper Studio. You can drop a column with one click in its table structure editor, and it’s free to use for PostgreSQL, download it here.

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:

Beekeeper Studio Is A Free & Open Source PostgreSQL GUI

This is the PostgreSQL client I’ve been looking for! The UI is what sets it apart: Clean, intuitive, powerful. Goodbye clunky PGAdmin. - David Cyprian

Beekeeper Studio is a fast, intuitive and easy to use PostgreSQL client. It supports loads of other databases too, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.

What PostgreSQL Users Say About Beekeeper Studio

Jay Simons Jay Simons Owner, Designly.biz

Unlike pgAdmin, Beekeeper is remarkably simple to use yet not lacking in features–and the price is right! The database backup feature is particularly useful. Just great software.

Alex M

Fabulous tool, the most practical IDE I’ve found to date for Postgres and MySql db admin - it just works, no fuss :-)

Ready to Improve Your PostgreSQL Workflow?

download Download Free