ALTER TABLE Schema Changes

You've built your tables, loaded data, and your application is humming along. But what happens when your boss asks for a new column, a column name is a typo waiting to haunt you, or a data type no longer fits? Dropping and recreating the table is a nightmare—you'd lose data, permissions, and indexes. That's exactly the pain ALTER TABLE solves: it lets you evolve your PostgreSQL schema in place, safely and incrementally, without rewriting everything from scratch. This lesson is your hands-on guide to using ALTER TABLE to modify schema with confidence, covering everything from adding columns to handling tricky edge cases.

The problem this lesson solves

Imagine you've deployed a users table with columns name and email. A month later, you need to store phone_number. Without ALTER TABLE, you'd have to:

1. Create a new table with the additional column.
2. Copy all existing data over.
3. Drop the old table.
4. Recreate all indexes, constraints, and permissions.

That process is risky, time-consuming, and error-prone—especially in production. Even worse, if you need to change a column's data type or its default value, you'd face the same vicious cycle. The problem is clear: schema evolution is inevitable, and doing it crudely breaks your database and your team's workflow. ALTER TABLE is the surgical tool that lets you make these changes directly, preserving your data and database objects while you modify the schema structure.

Core concept / mental model

Think of your table as a well-organized spreadsheet. ALTER TABLE is like editing that spreadsheet directly—inserting a new column, renaming a column header, or changing the number format for a column—without creating a whole new file and copying everything over. PostgreSQL locks the table during the operation, makes the structural change, and then releases it.

Under the hood, ALTER TABLE uses a schema lock to ensure that no other transactions interfere with the change. This lock is different from a row-level lock: it blocks other DDL (Data Definition Language) operations and often DML (Data Manipulation Language) too, depending on the specific action. For example, adding a column with a default value can cause a full table rewrite on older PostgreSQL versions, whereas in PostgreSQL 11+ it's often just a metadata change.

Here's a simple mental model: each ALTER TABLE command is a recipe with three parts:

1. What to change: the table name
2. The action: ADD COLUMN, DROP COLUMN, ALTER COLUMN, RENAME
3. The specifics: column name, data type, constraints, or new defaults

The beauty is that these actions are composable—you can chain them in a single ALTER TABLE statement to make multiple changes atomically, or run them separately for granular control.

How it works step by step

1. Verify current schema — Use \d table_name in psql or query information_schema.columns to see what exists.
2. Add a new column — Use ALTER TABLE table_name ADD COLUMN column_name data_type;
3. Drop a column — Use ALTER TABLE table_name DROP COLUMN column_name; (use IF EXISTS to avoid errors).
4. Change a column's data type — Use ALTER TABLE table_name ALTER COLUMN column_name TYPE new_type; (optionally with USING to cast values).
5. Rename a column — Use ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
6. Modify defaults and constraints — Use SET DEFAULT, DROP DEFAULT, SET NOT NULL, or ADD CONSTRAINT.
7. Commit or rollback — ALTER TABLE is transactional; you can wrap it in a transaction and roll back if something goes wrong.

Each step requires careful thought: adding a column without a default is fast, but adding a column with a default can cause a rewrite; dropping a column might fail if it's referenced by views or other tables; changing a type might need a USING clause to convert existing data correctly.

Hands-on walkthrough

Let's put this into practice. Suppose we have a simple employees table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    salary NUMERIC(10,2)
);

Now, let's add a department column, drop the salary column (oops, we didn't need it), and then add it back with a default value:

-- Add a new column
ALTER TABLE employees ADD COLUMN department TEXT;

-- Drop a column (use IF EXISTS to be safe)
ALTER TABLE employees DROP COLUMN IF EXISTS salary;

-- Re-add salary with a default
ALTER TABLE employees ADD COLUMN salary NUMERIC(10,2) DEFAULT 0.00;

You can also combine multiple actions into a single statement, which is atomic:

ALTER TABLE employees
    ADD COLUMN hire_date DATE,
    ALTER COLUMN department SET DEFAULT 'Engineering',
    ALTER COLUMN department SET NOT NULL;

To verify, query the table's structure:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'employees'
ORDER BY ordinal_position;

Expected output (excerpt):

 column_name | data_type      | is_nullable | column_default
-------------+----------------+-------------+----------------
 id          | integer        | NO          | nextval(...)
 name        | text           | NO          |
 department  | text           | NO          | 'Engineering'::text
 hire_date   | date           | YES         |
 salary      | numeric(10,2)  | YES         | 0.00

Pro tip: Always take a backup or wrap schema changes in a transaction during development. For production, use a migration tool that runs ALTER TABLE statements in a controlled order.

Now let's change a column's data type. Suppose salary is currently NUMERIC, but you want to use INTEGER for whole-dollar values:

ALTER TABLE employees
    ALTER COLUMN salary TYPE INTEGER USING salary::INTEGER;

The USING clause tells PostgreSQL how to convert existing data. Without it, the cast might fail on non-integer values.

Renaming is equally straightforward:

ALTER TABLE employees RENAME COLUMN salary TO annual_pay;

This is especially handy when you need to fix a naming mismatch without touching the data.

Compare options / when to choose what

In most cases, ALTER TABLE is the right tool. But if you're restructuring multiple tables with foreign keys, a migration script using ALTER TABLE (with proper ordering) is still better than recreating tables from scratch. For very large tables, you may want to use a dedicated procedure—like creating a new table, copying data, and swapping—to minimize downtime, but that's beyond this lesson.

Troubleshooting & edge cases

• Error: "column ... of relation ... does not exist" — You're trying to alter a column that doesn't exist. Use IF EXISTS with DROP or ADD variations, or double-check the spelling.

• Error: "cannot alter type of a column used by a view or rule" — If a view references the column, PostgreSQL refuses to change its type. Drop the view first, alter the column, then recreate the view.

• Error: "column ... contains null values" when setting NOT NULL — Your column has nulls. Option 1: UPDATE table SET column = default WHERE column IS NULL, then ALTER COLUMN ... SET NOT NULL. Option 2: add the column as nullable, update, then set NOT NULL.

• Performance issue on large tables — Adding a column with a default can lock the table and rewrite it on PostgreSQL < 11. For modern versions, it's fast, but changing a column type (e.g., from TEXT to VARCHAR) will rewrite the table. This can take time on millions of rows.

• Foreign key references — If a column you're dropping is part of a foreign key, the ALTER will fail. You must drop the constraint first, then the column, then optionally re-add the constraint if needed.

• Multiple changes in one statement — Use a single ALTER TABLE with comma-separated actions. They run atomically, so if any fails, all changes are rolled back.

What you learned & what's next

You've mastered the art of using ALTER TABLE to modify schema without losing sleep. You now know how to:

• Add, drop, and rename columns with minimal risk.
• Change data types safely using USING clauses.
• Set defaults and constraints to evolve your schema elegantly.
• Combine multiple alterations into one atomic statement.
• Troubleshoot common pitfalls like null values and view dependencies.

This skill is foundational for any serious PostgreSQL work—from quick schema tweaks to planning long-term migrations. You've also seen how ALTER TABLE integrates with pipeline concepts like transaction safety and locking.

Next in the track, you'll dive into managing indexes and query performance, where you'll learn how to keep your ALTERed tables running at lightning speed. You'll use ALTER TABLE to add indexes, which we've hinted at but will now explore in depth.

Now go ahead—alter something! Practice on a test table and watch your schema evolve gracefully.