Adding Database Columns

SQL
ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name data_type;

1. Identify target table and column requirements

   • Table name
   • Column name
   • Data type
   • Constraints (if any)

2. Check if column exists (optional but recommended)

   SQL
   SELECT column_name 
   FROM information_schema.columns 
   WHERE table_name = 'your_table' AND column_name = 'your_column';

3. Execute ALTER TABLE statement

   SQL
   ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name data_type;

4. Verify addition

   SQL
   \d table_name  -- PostgreSQL
   DESCRIBE table_name;  -- MySQL

Example 1: Input: Add a JSONB column for storing scores Output:

SQL
ALTER TABLE skills ADD COLUMN IF NOT EXISTS criterion_scores jsonb;

Example 2: Input: Add nullable text column with default Output:

SQL
ALTER TABLE users ADD COLUMN IF NOT EXISTS bio text DEFAULT '';

Example 3: Input: Add non-null integer with constraint Output:

SQL
ALTER TABLE products ADD COLUMN IF NOT EXISTS priority integer NOT NULL DEFAULT 1 CHECK (priority >= 1 AND priority <= 5);