PostgreSQL - ALTER TABLE Command

The ALTER TABLE command is used to change the definition of an existing table. This powerful command allows you to add, drop, or modify columns, as well as add, remove, or modify table constraints. In PostgreSQL, most DDL commands like ALTER TABLE are transactional, meaning you can wrap them in a BEGIN/COMMIT block to apply multiple changes at once or ROLLBACK if something goes wrong.

Note: Altering large tables can sometimes lock the table, blocking reads and writes. For production systems, consider using specialized tools or strategies like per-statement timeouts (SET lock_timeout) for zero-downtime migrations.

Modifying Columns

Here are the most common column-related operations.

1. Add a New Column

ALTER TABLE table_name ADD COLUMN column_name datatype [column_constraint];

-- Example: Add a 'hire_date' column to the 'employees' table
ALTER TABLE employees ADD COLUMN hire_date DATE;

2. Drop a Column

ALTER TABLE table_name DROP COLUMN [IF EXISTS] column_name;

-- Example: Remove the 'gender' column
ALTER TABLE employees DROP COLUMN IF EXISTS gender;

3. Rename a Column

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- Example: Rename 'salary' to 'annual_salary'
ALTER TABLE employees RENAME COLUMN salary TO annual_salary;

4. Change a Column’s Data Type

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype [USING expression];

-- Example: Change the employee ID to a larger integer type
ALTER TABLE employees ALTER COLUMN id TYPE BIGINT;

-- Example with a cast: Change a text column to integer
ALTER TABLE employees ALTER COLUMN employee_code TYPE INT USING employee_code::integer;

5. Set/Remove a Default Value

-- Set a default value
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

-- Remove a default value
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

-- Example: Set default status for new employees
ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active';

Modifying Constraints

Constraints enforce data integrity rules. They can be added or removed at any time.

1. Add a NOT NULL Constraint

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

-- Example: Make the 'name' column mandatory
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;

2. Add a UNIQUE Constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

-- Example: Ensure all employee email addresses are unique
ALTER TABLE employees ADD CONSTRAINT uq_employees_email UNIQUE (email);

3. Add a PRIMARY KEY Constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, ...);

-- Example: Add a primary key to a table that doesn't have one
ALTER TABLE departments ADD CONSTRAINT pk_departments PRIMARY KEY (id);

4. Add a FOREIGN KEY Constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
REFERENCES other_table (other_column_name);

-- Example: Link employees to their department
ALTER TABLE employees ADD CONSTRAINT fk_employees_department
FOREIGN KEY (department_id) REFERENCES departments (id);

5. Add a CHECK Constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

-- Example: Ensure salary is always a positive number
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (annual_salary > 0);

6. Drop any Constraint

ALTER TABLE table_name DROP CONSTRAINT [IF EXISTS] constraint_name;

-- Example: Remove the unique email constraint
ALTER TABLE employees DROP CONSTRAINT IF EXISTS uq_employees_email;