ALTER TABLE in PostgreSQL

ALTER TABLE modifies the structure of a table that already exists — and already has data in it.

Adding a Column

ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

Add a column with a default applied retroactively to every existing row:

ALTER TABLE employees ADD COLUMN is_active BOOLEAN DEFAULT true;

Dropping a Column

ALTER TABLE employees DROP COLUMN phone;

This permanently deletes the column and every value stored in it — there's no undo.

Renaming a Column

ALTER TABLE employees RENAME COLUMN department TO team;

Renaming a Table

ALTER TABLE employees RENAME TO staff;

Changing a Column's Data Type

ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);

If the existing data can't convert automatically, add a USING clause telling Postgres how to convert it:

ALTER TABLE employees ALTER COLUMN id TYPE TEXT USING id::TEXT;

Adding / Dropping Constraints

ALTER TABLE employees ALTER COLUMN email SET NOT NULL;
ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE employees DROP CONSTRAINT uq_email;

Setting or Removing a Default

ALTER TABLE employees ALTER COLUMN department SET DEFAULT 'Unassigned';
ALTER TABLE employees ALTER COLUMN department DROP DEFAULT;

Common Errors & Troubleshooting

• ERROR: column "salary" cannot be cast automatically to type integer — happens when the target type can't unambiguously convert existing data; add an explicit USING column::new_type (or a more specific expression) telling Postgres exactly how to convert each value.
• ERROR: column "email" contains null values when adding NOT NULL — existing rows already have NULL in that column; either backfill them with a value first, or add the constraint with a DEFAULT applied retroactively.
• ALTER TABLE appearing to hang on a huge table — some alterations (like adding a column with a non-constant default in older Postgres versions, or changing a column's type) require rewriting the entire table, which can take a long time and lock it — plan these for low-traffic periods on large production tables.

Key Takeaways

• ALTER TABLE can chain multiple changes in one statement, but each is also valid on its own.
• Changing a column's type may need an explicit USING clause if the conversion isn't automatic.
• Dropping a column is permanent and instant — back up important data before running it in production.