Kenhuang Academy
  • English

      • 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.