Altering Tables in PostgreSQL

Overview

ALTER TABLE is used to modify the structure of an existing table, including adding, dropping, or modifying columns, and adding or removing constraints.

Adding Columns

-- Add a single column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add a column with a default value
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Add a column with a constraint
ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL;

-- Add multiple columns
ALTER TABLE users 
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at TIMESTAMP;

Dropping Columns

-- Drop a single column
ALTER TABLE users DROP COLUMN phone;

-- Drop multiple columns
ALTER TABLE users 
    DROP COLUMN phone,
    DROP COLUMN fax;

-- Cascade drop (drop dependent objects)
ALTER TABLE users DROP COLUMN email CASCADE;

-- Drop column if it exists
ALTER TABLE users DROP COLUMN IF EXISTS phone;

Modifying Columns

Renaming Columns

-- Rename a column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Rename multiple columns
ALTER TABLE users RENAME COLUMN addr TO address;
ALTER TABLE users RENAME COLUMN tel TO phone;

Changing Data Types

-- Change column data type
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;

-- Convert data using USING
ALTER TABLE users 
    ALTER COLUMN price TYPE NUMERIC(10,2) 
    USING price::NUMERIC(10,2);

-- Change string length
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);

Setting/Removing Default Values

-- Set default value
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Remove default value
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Set current timestamp as default value
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

Setting/Removing NOT NULL Constraints

-- Set NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

Adding Constraints

Primary Key Constraints

-- Add primary key
ALTER TABLE users ADD PRIMARY KEY (id);

-- Add named primary key
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);

-- Add composite primary key
ALTER TABLE order_items 
    ADD PRIMARY KEY (order_id, product_id);

Foreign Key Constraints

-- Add foreign key
ALTER TABLE orders 
    ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Add named foreign key
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id);

-- Add foreign key with cascade behavior
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

Unique Constraints

-- Add unique constraint
ALTER TABLE users ADD UNIQUE (email);

-- Add named unique constraint
ALTER TABLE users 
    ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Add composite unique constraint
ALTER TABLE products 
    ADD CONSTRAINT products_name_category_unique 
    UNIQUE (name, category_id);

Check Constraints

-- Add check constraint
ALTER TABLE products 
    ADD CHECK (price > 0);

-- Add named check constraint
ALTER TABLE products 
    ADD CONSTRAINT products_price_check 
    CHECK (price > 0 AND price < 1000000);

-- Add complex check constraint
ALTER TABLE users 
    ADD CONSTRAINT users_age_check 
    CHECK (age >= 18 AND age <= 120);

Dropping Constraints

-- Drop primary key
ALTER TABLE users DROP CONSTRAINT users_pkey;

-- Drop foreign key
ALTER TABLE orders DROP CONSTRAINT fk_orders_users;

-- Drop unique constraint
ALTER TABLE users DROP CONSTRAINT users_email_unique;

-- Drop check constraint
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- Cascade drop constraint
ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;

Renaming Tables

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Rename table (if exists)
ALTER TABLE IF EXISTS old_table RENAME TO new_table;

Changing Table Ownership

-- Change table owner
ALTER TABLE users OWNER TO new_owner;

-- Change owner of multiple tables
ALTER TABLE users OWNER TO admin;
ALTER TABLE orders OWNER TO admin;

Changing Table Tablespace

-- Move table to a different tablespace
ALTER TABLE users SET TABLESPACE fast_storage;

-- Move table and its indexes
ALTER TABLE users SET TABLESPACE fast_storage;

Practical Examples

Example 1: Refactoring the Users Table

-- Add new columns
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
ALTER TABLE users ADD COLUMN login_count INTEGER DEFAULT 0;

-- Modify existing columns
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Add check constraint
ALTER TABLE users ADD CONSTRAINT users_age_check CHECK (age >= 18);

-- Rename column
ALTER TABLE users RENAME COLUMN addr TO address;

Example 2: Optimizing the Orders Table

-- Add foreign key constraint
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE;

-- Add status check
ALTER TABLE orders 
    ADD CONSTRAINT orders_status_check 
    CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- Add amount check
ALTER TABLE orders 
    ADD CONSTRAINT orders_amount_check 
    CHECK (total_amount >= 0);

-- Add timestamps
ALTER TABLE orders 
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Example 3: Extending the Products Table

-- Add new fields
ALTER TABLE products 
    ADD COLUMN description TEXT,
    ADD COLUMN image_url VARCHAR(500),
    ADD COLUMN is_active BOOLEAN DEFAULT true;

-- Modify price column
ALTER TABLE products 
    ALTER COLUMN price TYPE NUMERIC(10,2),
    ALTER COLUMN price SET NOT NULL;

-- Add constraints
ALTER TABLE products 
    ADD CONSTRAINT products_price_check CHECK (price > 0),
    ADD CONSTRAINT products_stock_check CHECK (stock >= 0);

-- Add unique constraint
ALTER TABLE products 
    ADD CONSTRAINT products_sku_unique UNIQUE (sku);

Best Practices

1. Backup Data: Always back up data before modifying table structures
2. Use Transactions: Execute ALTER TABLE operations within a transaction
3. Name Constraints: Assign meaningful names to constraints
4. Test in Staging: Test modifications in a staging environment first
5. Avoid Table Locking: Be aware that some operations lock the entire table
6. Use IF EXISTS: Use IF EXISTS to avoid errors

Considerations

• ALTER TABLE operations may lock the table
• Some modifications require rewriting the entire table
• A default value must be provided (or ensure no NULL values exist) when adding a NOT NULL column
• Foreign key constraints impact performance
• Structural modifications to large tables may take a significant amount of time

Summary

ALTER TABLE is a powerful tool for modifying table structures in PostgreSQL:

• Add/Drop Columns: Extend or streamline table structures
• Modify Column Properties: Change data types, default values, and NULL constraints
• Manage Constraints: Add or remove primary keys, foreign keys, unique, and check constraints
• Rename: Rename tables and columns
• Other Operations: Change ownership, tablespace, etc.

Using ALTER TABLE appropriately allows flexible adjustment of database structures to meet business requirements.