Drop Column in PostgreSQL

ALTER TABLE ... DROP COLUMN removes one or more columns from an existing table. PostgreSQL automatically removes single-column indexes and constraints on the dropped column. If other objects — views, triggers, stored functions — reference the column, you must either update those objects first or use CASCADE to drop them along with the column.

Syntax

-- Drop a single column
ALTER TABLE table_name DROP COLUMN column_name;

-- Drop only if it exists (no error if missing)
ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;

-- Drop and cascade to dependent objects
ALTER TABLE table_name DROP COLUMN column_name CASCADE;

-- Drop multiple columns at once
ALTER TABLE table_name
  DROP COLUMN col1,
  DROP COLUMN col2;

Practical Example

Set up tables and a view with column dependencies:

CREATE TABLE categories (
  category_id  SERIAL PRIMARY KEY,
  name         VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  name         VARCHAR(200) NOT NULL,
  category_id  INT NOT NULL REFERENCES categories(category_id),
  internal_ref VARCHAR(50),
  notes        TEXT,
  barcode      VARCHAR(50)
);

CREATE VIEW product_catalog AS
  SELECT p.product_id, p.name, c.name AS category_name, p.barcode
  FROM products p
  JOIN categories c USING (category_id);

Drop a column that has a foreign key constraint — the constraint is removed automatically:

ALTER TABLE products DROP COLUMN IF EXISTS category_id;

Attempt to drop a column referenced by the view (fails without CASCADE):

ALTER TABLE products DROP COLUMN barcode;
-- ERROR: cannot drop table products column barcode because other objects depend on it
-- DETAIL: view product_catalog depends on table products column barcode
-- HINT: Use DROP ... CASCADE to drop the dependent objects too.

Use CASCADE to drop the column and the dependent view:

ALTER TABLE products DROP COLUMN barcode CASCADE;
-- NOTICE: drop cascades to view product_catalog

Drop multiple columns simultaneously (one lock acquisition):

ALTER TABLE products
  DROP COLUMN internal_ref,
  DROP COLUMN notes;

Confirm the final structure:

\d products

Reclaim disk space after dropping large columns:

VACUUM FULL products;

Disk Space and VACUUM

Dropping a column marks its data as invisible but does not immediately free the storage. The space is reclaimed by autovacuum over time, or immediately by VACUUM FULL. Note that VACUUM FULL requires an exclusive lock and rewrites the table, so schedule it during a maintenance window for large tables.

-- Check bloat before and after
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relname = 'products';

Testing with Vela

Before dropping columns in production, Vela database branching lets you run the operation on a production-data clone to confirm exactly which dependent objects will be cascaded, measure how long VACUUM FULL takes to reclaim space, and verify that application queries against the updated schema still work correctly.

Production Tips

• Dropping a column does not immediately reclaim disk space — run vacuum or VACUUM FULL to reclaim it, keeping in mind VACUUM FULL requires an exclusive lock.
• Use IF EXISTS in deployment scripts to make them idempotent — the statement becomes a no-op if the column was already dropped.
• Review dependencies with \d+ table_name or query pg_depend before adding CASCADE — it drops all referencing views, functions, and rules.
• Drop multiple columns in one ALTER TABLE statement to minimize the number of lock acquisitions.
• For large tables, consider whether dropping the column is urgent or can be deferred to a maintenance window that also runs VACUUM FULL.