PostgreSQL ALTER COLUMN

Key ALTER COLUMN Operations

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

ALTER TABLE users
ALTER COLUMN status
SET DEFAULT 'active';

ALTER TABLE employees
ALTER COLUMN email
SET NOT NULL;

ALTER TABLE orders
ALTER COLUMN status
DROP DEFAULT;

🔹 Change Column Data Type

You can change a column's data type using ALTER COLUMN TYPE. PostgreSQL will attempt to convert existing data to the new type. If conversion fails, you may need to use USING clause for custom conversion logic.

-- Change column type
ALTER TABLE products
ALTER COLUMN product_name TYPE VARCHAR(200);

-- Change with USING clause for conversion
ALTER TABLE orders
ALTER COLUMN total_amount TYPE INTEGER
USING total_amount::INTEGER;

🔹 Set Default Value

Setting a default value affects only future INSERT operations. Existing rows are not changed. The default value is used when no value is specified during insertion, simplifying data entry and ensuring consistency.

-- Set default value for existing column
ALTER TABLE employees
ALTER COLUMN is_active SET DEFAULT true;

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

🔹 Drop Default Value

Removing a default value means future inserts must explicitly provide a value for that column or it will be NULL. This gives you more control over data entry. Existing rows remain unchanged when you drop defaults.

-- Remove default value
ALTER TABLE products
ALTER COLUMN discount_percent DROP DEFAULT;

-- Remove timestamp default
ALTER TABLE users
ALTER COLUMN created_at DROP DEFAULT;

🔹 Add NOT NULL Constraint

Adding NOT NULL ensures the column cannot contain NULL values. Before adding this constraint, make sure all existing rows have non-NULL values in that column. Otherwise, the operation will fail with an error message.

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

-- First update NULLs, then add constraint
UPDATE employees SET department = 'Unassigned' WHERE department IS NULL;
ALTER TABLE employees
ALTER COLUMN department SET NOT NULL;

🔹 Remove NOT NULL Constraint

Dropping NOT NULL allows the column to accept NULL values again. This is useful when requirements change and you need more flexibility. It's a simple operation that doesn't affect existing data in the column.

-- Remove NOT NULL constraint
ALTER TABLE products
ALTER COLUMN description DROP NOT NULL;

-- Allow NULL in previously required field
ALTER TABLE employees
ALTER COLUMN middle_name DROP NOT NULL;

🔹 Rename Column

You can rename a column using ALTER TABLE RENAME COLUMN. This changes the column name throughout the database. All views, functions, and queries referencing the old name will need to be updated manually.

-- Rename a column
ALTER TABLE users
RENAME COLUMN phone TO phone_number;

-- Rename for clarity
ALTER TABLE products
RENAME COLUMN desc TO description;

🔹 Multiple Column Alterations

You can perform multiple alterations in a single ALTER TABLE statement. This is more efficient and reduces table locks. Combine different operations like changing types, setting defaults, and adding constraints together for better performance.

-- Multiple alterations at once
ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(12,2),
ALTER COLUMN salary SET NOT NULL,
ALTER COLUMN hire_date SET DEFAULT CURRENT_DATE,
ALTER COLUMN is_active SET DEFAULT true;