Altering Tables

Schemas change as an application grows, and ALTER TABLE is the tool for evolving a table after it already has data in it — adding or dropping columns, changing a column's type, renaming things, and adding or dropping constraints. Each of these is a distinct sub-command run against the same ALTER TABLE statement.

Adding a column

ADD COLUMN appends a new column to an existing table. If existing rows need a value for it, give it a DEFAULT — otherwise every existing row gets NULL for the new column.

Adding a discount column to products

SQL

ALTER TABLE products
  ADD COLUMN discount_pct NUMERIC(5, 2) NOT NULL DEFAULT 0;

Dropping a column

DROP COLUMN removes a column and every value stored in it, in one statement.

Removing an unused column

SQL

ALTER TABLE products
  DROP COLUMN description;

DROP COLUMN destroys data immediately and irreversibly

There is no undo. Once a column is dropped, its data is gone unless you have a backup to restore from. Double-check the column name and table, and consider taking a backup or exporting the column's data first if there is any chance it will be needed later.

Changing a column's type

ALTER COLUMN ... TYPE changes the declared type of a column. For simple, compatible conversions (widening an INTEGER to a BIGINT, for instance), PostgreSQL figures out the conversion on its own. For anything less obvious, it needs to be told explicitly how to turn the old values into the new type, using a USING clause.

Converting a text column to numeric

SQL

ALTER TABLE products
  ALTER COLUMN unit_price TYPE NUMERIC(10, 2)
  USING unit_price::NUMERIC(10, 2);

Why USING is sometimes required

PostgreSQL will not guess how to convert data when the conversion could lose information or is ambiguous — for example, turning a TEXT column into NUMERIC requires deciding what to do with any values that are not valid numbers. USING lets you supply that expression explicitly, whether it is a simple cast like ::NUMERIC or something more involved, such as stripping currency symbols first.

Renaming a column or table

Renaming

SQL

ALTER TABLE products RENAME COLUMN unit_price TO price;
ALTER TABLE products RENAME TO catalog_products;

ALTER TABLE
ALTER TABLE

Adding and dropping constraints

Adding a CHECK constraint, then removing it

SQL

ALTER TABLE products
  ADD CONSTRAINT price_non_negative CHECK (price >= 0);

ALTER TABLE products
  DROP CONSTRAINT price_non_negative;

Metadata-only changes vs. table rewrites

Not all ALTER TABLE operations cost the same. Some are pure metadata changes that complete almost instantly no matter how large the table is; others require PostgreSQL to rewrite every row of the table, which can take a long time and, worse, hold a lock that blocks other queries for the duration.

Operation	Typical cost
ADD COLUMN with no default, or a constant default (PG 11+)	Metadata-only — fast
RENAME COLUMN / RENAME TABLE	Metadata-only — fast
DROP COLUMN	Metadata-only (data is hidden, reclaimed later by VACUUM)
ALTER COLUMN ... TYPE (incompatible types)	Full table rewrite — slow, holds a lock
ADD COLUMN with a volatile default (e.g. random())	Full table rewrite
ADD CONSTRAINT ... CHECK / NOT NULL on existing data	Full table scan to validate, though not always a rewrite

Table rewrites lock the table in production

On a large, actively used table, an operation that triggers a rewrite can hold an exclusive lock for minutes or hours, blocking reads and writes for the whole time. Before running an ALTER TABLE against a big production table, check whether the specific operation is metadata-only or a rewrite, and consider running it during low-traffic hours or using an online-migration strategy for the risky ones.

ADD COLUMN and DROP COLUMN change a table's shape; dropping a column deletes its data permanently.
ALTER COLUMN ... TYPE may need an explicit USING clause to convert existing values.
RENAME COLUMN and RENAME TABLE just relabel things.
Some ALTER TABLE operations are fast metadata changes; others rewrite the whole table and lock it — know which kind you are running before doing it on a large production table.