# ALTER TABLE and DROP

> Change tables and constraints while understanding rewrite and locking costs.

---

LLMS index: [llms.txt](/en/llms.txt)

---

Tables evolve. `ALTER TABLE` changes an existing table: add or drop a column, rename a column or the table itself, add or drop a constraint, change a column's type, change its default. `DROP TABLE` removes the table and every row it contains. Schema changes in PostgreSQL are transactional — a failed `ALTER TABLE` rolls back cleanly, and you can wrap several `ALTER`s in a `BEGIN ... COMMIT` to apply them atomically.

## What you'll learn {#learn}

- Adding, renaming, and dropping columns
- Adding and dropping constraints
- Changing a column's type with `USING`
- Renaming a table
- The locking and rewrite implications of common alterations

## Example {#example}

```sql {num="18.1" caption="ALTER TABLE and DROP"}
-- ADD COLUMN: new columns appear at the end
ALTER TABLE products ADD COLUMN description text;

-- ADD COLUMN with a default (cheap in PG 11+ for constant defaults)
ALTER TABLE products ADD COLUMN sku text NOT NULL DEFAULT '';

-- Rename a column
ALTER TABLE products RENAME COLUMN description TO short_desc;

-- Change a column type (use USING when a cast is needed)
ALTER TABLE products ALTER COLUMN sku TYPE varchar(40);

-- Add a constraint after the fact
ALTER TABLE products ADD CONSTRAINT sku_format CHECK (sku ~ '^[A-Z0-9-]*$');

-- Drop a column
ALTER TABLE products DROP COLUMN short_desc;

-- Rename the table itself
ALTER TABLE products RENAME TO products_old;
ALTER TABLE products_old RENAME TO products;

-- DROP TABLE removes the table and all data
-- (commented out so the rest of the lessons can still use products)
-- DROP TABLE products;

SELECT tablename FROM pg_tables WHERE tablename = 'products';
```

Each `ALTER TABLE` is a single transactional statement. You can also batch multiple operations: `ALTER TABLE t ADD COLUMN a int, ADD COLUMN b text, DROP COLUMN c;` — running them in one statement is faster because the table is touched once.

Two operations are worth understanding in terms of cost:

- **Adding a column with no default (or a constant default) is fast** — PostgreSQL just records the new column metadata.
- **Many column type changes rewrite the table.** `ALTER COLUMN x TYPE bigint` reads and rewrites every row. On a large table this can take hours and holds an exclusive lock the whole time. Some binary-compatible changes avoid a rewrite. For a rewrite-heavy change, plan a migration such as new column → backfill → switch; `pg_repack` does not perform arbitrary type conversions.

`ALTER TABLE ... ALTER COLUMN col TYPE new_type USING expr` lets you specify how to convert the existing value: `ALTER COLUMN price TYPE numeric(12,2) USING price::numeric(12,2)`.

## Run the example {#run}

To run:

```text
$ psql -f source/alter-table-and-drop.sql postgres
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 tablename
-----------
 products
(1 row)
```

## Common pitfalls {#pitfalls}

- Adding `NOT NULL` to an existing column requires that the column already has no NULLs and acquires an `ACCESS EXCLUSIVE` lock long enough to verify. Backfill first, then `SET NOT NULL`. From PostgreSQL 12, you can `ALTER COLUMN x SET NOT NULL` faster if a matching `CHECK` constraint exists.
- Dropping a column does not reclaim disk space immediately — the column is marked dead. Run `VACUUM FULL` (heavyweight) or recreate the table to reclaim it.
- Renaming a column updates catalog-tracked dependencies such as views, but can break application SQL and function bodies stored as text. Review those uses before renaming.
- `ALTER TYPE` on an `ENUM` cannot remove values; adding values is fine.

> [!TIP] Tip
> For zero-downtime schema changes, the standard pattern is: add the new column → backfill in batches → make the application write to both → switch reads → drop the old column. Tools like Liquibase, Flyway, or `pg_repack` help; the database does the locking, you do the choreography.

> [!NOTE] Try it
> Add a column back: `ALTER TABLE products ADD COLUMN notes text;`. Then rename: `ALTER TABLE products RENAME COLUMN notes TO remarks;`. Then drop: `ALTER TABLE products DROP COLUMN remarks;`. Inspect with `\d products` in psql.

## Downloads {#downloads}

SQL file: [alter-table-and-drop.sql](/examples/source/alter-table-and-drop.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)