PostgreSQL ADD COLUMN
Learn how to add new columns to existing tables
➕ What is ADD COLUMN?
ADD COLUMN is used with ALTER TABLE to add new columns to existing tables. This allows you to expand your database schema without recreating tables or losing existing data.
-- Add a phone column to users table
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
Output:
✓ ALTER TABLE (column "phone" added)
Key ADD COLUMN Concepts
Basic Column
Add simple column with data type
ALTER TABLE products
ADD COLUMN description TEXT;With Default
Add column with default value
ALTER TABLE users
ADD COLUMN status VARCHAR(20)
DEFAULT 'active';With Constraint
Add column with NOT NULL
ALTER TABLE orders
ADD COLUMN tracking_number
VARCHAR(50) NOT NULL;Multiple Columns
Add several columns at once
ALTER TABLE employees
ADD COLUMN department VARCHAR(50),
ADD COLUMN hire_date DATE;🔹 Add Single Column
The simplest form adds one column with a data type. The new column is added to all existing rows with NULL values unless you specify a default. This is the most common table modification operation.
-- Add a single column
ALTER TABLE products
ADD COLUMN category VARCHAR(100);
Output:
✓ ALTER TABLE
🔹 Add Column with Default Value
Adding a default value ensures existing rows get a specific value instead of NULL. This is useful when you need all records to have meaningful data. The default applies to future inserts as well.
-- Add column with default value
ALTER TABLE employees
ADD COLUMN is_active BOOLEAN DEFAULT true;
-- Add column with timestamp default
ALTER TABLE orders
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
Output:
✓ ALTER TABLE (column added with default value)
🔹 Add Column with NOT NULL
When adding a NOT NULL column to a table with existing data, you must provide a default value. Otherwise, PostgreSQL cannot populate existing rows. This ensures data integrity across all records in your table.
-- Add NOT NULL column with default
ALTER TABLE users
ADD COLUMN account_type VARCHAR(20) NOT NULL DEFAULT 'standard';
-- This will fail if table has data and no default
-- ALTER TABLE users
-- ADD COLUMN required_field VARCHAR(50) NOT NULL;
Output:
✓ ALTER TABLE (NOT NULL column added)
🔹 Add Multiple Columns
You can add multiple columns in a single ALTER TABLE statement by separating them with commas. This is more efficient than multiple separate statements. It reduces database locks and improves performance for large tables.
-- Add multiple columns at once
ALTER TABLE products
ADD COLUMN brand VARCHAR(100),
ADD COLUMN weight DECIMAL(10, 2),
ADD COLUMN dimensions VARCHAR(50),
ADD COLUMN is_featured BOOLEAN DEFAULT false;
Output:
✓ ALTER TABLE (4 columns added)
🔹 Add Column with CHECK Constraint
CHECK constraints validate data when inserting or updating. Adding them with new columns ensures data quality from the start. This prevents invalid data from entering your database and maintains business rules.
-- Add column with CHECK constraint
ALTER TABLE products
ADD COLUMN discount_percent INTEGER
CHECK (discount_percent >= 0 AND discount_percent <= 100);
-- Add column with multiple constraints
ALTER TABLE employees
ADD COLUMN age INTEGER
CHECK (age >= 18 AND age <= 100)
DEFAULT 25;
Output:
✓ ALTER TABLE (column with constraint added)
🔹 Add Column IF NOT EXISTS
The IF NOT EXISTS clause prevents errors if the column already exists. This makes your migration scripts idempotent and safe to run multiple times. It's a best practice for database schema management.
-- Safe column addition
ALTER TABLE users
ADD COLUMN IF NOT EXISTS middle_name VARCHAR(50);
-- Won't cause error if column exists
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS notes TEXT;
Output:
✓ ALTER TABLE (column added or already exists)