How to Drop a Column in PostgreSQL

Why would you drop a column?

Free disk space, simplify the schema, or remove sensitive data no longer required. Dropping eliminates the column and its contents forever, so create a backup first.

What is the basic DROP COLUMN syntax?

Use ALTER TABLE followed by the table name, then DROP COLUMN and the column name. Add IF EXISTS to avoid errors when the column might already be gone.

Syntax breakdown

ALTER TABLE instructs PostgreSQL to change a table; DROP COLUMN removes the specified column; CASCADE drops dependent objects like indexes or views, while RESTRICT (default) blocks the action if dependencies exist.

How do you safely remove a column?

1️⃣ Validate no application code or queries reference the column. 2️⃣ Check dependencies with pg_depend or psql \d+. 3️⃣ Issue ALTER TABLE … DROP COLUMN inside a transaction so you can ROLLBACK if needed.

Step-by-step example

BEGIN; ALTER TABLE "Orders" DROP COLUMN shipping_method; COMMIT; The transaction ensures either full success or no change.

Does DROP COLUMN lock the table?

Yes, PostgreSQL takes an ACCESS EXCLUSIVE lock for a very short time. Reads and writes wait during the catalog update, but the lock is usually milliseconds unless the table is huge.

Can you drop multiple columns at once?

Yes. Separate each DROP COLUMN clause with a comma: ALTER TABLE "Products" DROP COLUMN supplier_id, DROP COLUMN discontinued_at;

Best practices

Back up first, drop in low-traffic windows, monitor replication lag, test in staging, and always use IF EXISTS in automated scripts.

Common mistakes to avoid

Using CASCADE blindly can delete views or foreign keys you still need. Forgetting to update ORM models causes runtime errors after deployment.