ALTER TABLE

Tables are not set in stone. Requirements change, new features demand new columns, old columns become obsolete, and data types need adjusting. ALTER TABLE lets you modify an existing table's structure without recreating it.

Adding Columns

ADD COLUMN

The most common alteration is adding a new column:

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

This adds a phone column to the employees table. All existing rows get NULL in this new column.

Adding a Column with a Default

ALTER TABLE employees
ADD COLUMN country VARCHAR(50) DEFAULT 'USA';

All existing rows get the value 'USA' in the new column. New rows that do not specify a country will also get 'USA'.

Adding a Column with NOT NULL

If you add a NOT NULL column, you must provide a DEFAULT (otherwise existing rows would violate the constraint):

ALTER TABLE employees
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

This works because the default ensures existing rows are not NULL.

Without a default, this would fail:

-- This FAILS if the table already has rows:
ALTER TABLE employees
ADD COLUMN status VARCHAR(20) NOT NULL;
-- ERROR: column "status" of relation "employees" contains null values

Adding Multiple Columns

You can add several columns in one statement:

ALTER TABLE products
ADD COLUMN weight NUMERIC(8, 2),
ADD COLUMN dimensions TEXT,
ADD COLUMN sku VARCHAR(50) UNIQUE;

Dropping Columns

DROP COLUMN

Remove a column and all its data:

ALTER TABLE employees
DROP COLUMN phone;

This is permanent. The column and all its data are gone. There is no "undo" without a backup.

DROP COLUMN IF EXISTS

Avoid an error if the column does not exist:

ALTER TABLE employees
DROP COLUMN IF EXISTS phone;

Dropping Multiple Columns

ALTER TABLE products
DROP COLUMN weight,
DROP COLUMN dimensions;

CASCADE with DROP COLUMN

If other objects (views, indexes, foreign keys) depend on the column, you must use CASCADE:

ALTER TABLE employees
DROP COLUMN department CASCADE;

CASCADE automatically drops dependent objects. Without it, PostgreSQL raises an error listing the dependencies.

Warning: Be very careful with CASCADE in production. It can drop views, constraints, and other objects that depend on the column.

Renaming Columns

RENAME COLUMN

Change a column's name without affecting its data or type:

ALTER TABLE employees
RENAME COLUMN name TO full_name;

All existing data is preserved. However, any queries, views, or application code that reference the old column name will break.

Renaming Tables

You can also rename the entire table:

ALTER TABLE employees
RENAME TO staff;

After this, the employees table no longer exists -- it is now called staff.

Changing Column Types

ALTER COLUMN ... TYPE

Change a column's data type:

ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(12, 2);

This changes the price column from whatever it was to NUMERIC(12, 2).

Type Changes That Require USING

Some type conversions are not automatic. You need USING to tell PostgreSQL how to convert:

-- Convert a TEXT column to INTEGER
ALTER TABLE items
ALTER COLUMN quantity TYPE INTEGER USING quantity::INTEGER;

-- Convert a VARCHAR to a DATE
ALTER TABLE events
ALTER COLUMN event_date TYPE DATE USING event_date::DATE;

The USING clause provides an expression that converts each existing value to the new type.

Widening vs. Narrowing

Widening (increasing capacity) is generally safe:

-- VARCHAR(50) to VARCHAR(200) -- always safe
ALTER TABLE users
ALTER COLUMN username TYPE VARCHAR(200);

-- INTEGER to BIGINT -- always safe
ALTER TABLE orders
ALTER COLUMN id TYPE BIGINT;

Narrowing (decreasing capacity) can fail if existing data does not fit:

-- VARCHAR(200) to VARCHAR(50) -- fails if any value exceeds 50 chars
ALTER TABLE users
ALTER COLUMN username TYPE VARCHAR(50);

Modifying Constraints

Adding NOT NULL

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

This fails if any existing rows have NULL in the email column. You must update those rows first:

-- First, fix existing NULLs
UPDATE employees SET email = 'unknown@example.com' WHERE email IS NULL;

-- Then add the constraint
ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

Removing NOT NULL

ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

Setting a Default

ALTER TABLE employees
ALTER COLUMN is_active SET DEFAULT TRUE;

This only affects future inserts. Existing rows are not changed.

Removing a Default

ALTER TABLE employees
ALTER COLUMN is_active DROP DEFAULT;

Adding a CHECK Constraint

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

Adding a UNIQUE Constraint

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

Dropping a Constraint by Name

ALTER TABLE products
DROP CONSTRAINT positive_price;

Combining Multiple Alterations

You can perform several changes in a single ALTER TABLE statement:

ALTER TABLE employees
    ADD COLUMN middle_name VARCHAR(100),
    ADD COLUMN nickname VARCHAR(50),
    ALTER COLUMN email SET NOT NULL,
    ALTER COLUMN salary SET DEFAULT 0;

This is more efficient than running four separate statements because PostgreSQL processes them in a single pass.

Practical Migration Example

Imagine you need to evolve a simple users table over time:

-- Original table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);

-- Sprint 1: Add phone and make email required
ALTER TABLE users
    ADD COLUMN phone VARCHAR(20),
    ALTER COLUMN email SET NOT NULL;

-- Sprint 2: Add creation timestamp and user role
ALTER TABLE users
    ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW(),
    ADD COLUMN role VARCHAR(20) DEFAULT 'user';

-- Sprint 3: Rename 'name' to 'full_name' and add 'username'
ALTER TABLE users
    RENAME COLUMN name TO full_name;
ALTER TABLE users
    ADD COLUMN username VARCHAR(50) UNIQUE;

-- Sprint 4: Remove phone column (turned out we didn't need it)
ALTER TABLE users
    DROP COLUMN IF EXISTS phone;

Try It Yourself

Think through these scenarios:

1. You have a posts table and need to add a view_count column that starts at 0 and cannot be negative. What would the ALTER TABLE statement look like?

ALTER TABLE posts
ADD COLUMN view_count INTEGER NOT NULL DEFAULT 0 CHECK (view_count >= 0);

2. You need to rename the title column to headline in a news table. Write the statement.

ALTER TABLE news
RENAME COLUMN title TO headline;

3. A price column is currently INTEGER but needs to store decimal values. How do you change it?

ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(10, 2);