Snowflake ADD COLUMN: How to Add Columns to a Table
The ADD COLUMN command in Snowflake allows you to modify the schema of existing tables by adding new columns. You can specify various attributes for the columns, such as default values, constraints, collation specifications, masking policies, and tags.
Basic Usage
_10ALTER TABLE [table_name] ADD [COLUMN] [IF NOT EXISTS] [column_name] [data_type] [options];
Options:
- DEFAULT: Sets a default value for the column. This must be a literal value, not an expression or function result.
- AUTOINCREMENT: Automatically increments the column value for new records.
- CONSTRAINTS: Includes constraints like UNIQUE, PRIMARY KEY, or FOREIGN KEY.
- COLLATE: Defines collation specification for string comparison.
- MASKING POLICY: Applies a data masking policy to the column.
- TAG: Assigns metadata tags to the column.
Notes
- When setting a default value, ensure it matches the column’s data type; otherwise, it will result in an error.
- You cannot specify
IF NOT EXISTSif you are also specifyingDEFAULT,AUTOINCREMENT, or any constraints.
Examples
Add a Simple Column with a Default Value
To add a basic column with a default literal value:
Snowflake SQL Query
_10ALTER TABLE employees ADD COLUMN age INT DEFAULT 30;
Explanation
This command adds the age column with a default value of 30 to the employees table.
Add a Column with a Primary Key Constraint
To ensure that values in the new column are unique and not null:
Snowflake SQL Query
_10ALTER TABLE employees ADD COLUMN employee_id INT PRIMARY KEY;
Explanation
This designates employee_id as the primary key of the employees table.
Add Multiple Columns
To add more than one column in a single command:
Snowflake SQL Query
_10ALTER TABLE employees ADD COLUMN start_date DATE, end_date DATE;
Explanation
This adds both start_date and end_date columns to the employees table.
Add a Column Conditionally with a Default Value
To add a column only if it does not already exists:
Snowflake SQL Query
_10ALTER TABLE employees ADD COLUMN IF NOT EXISTS department VARCHAR DEFAULT 'Engineering';
Explanation
This conditionally adds the department column with ‘Engineering’ as the default value if it is not already present.
Add a Column with a Masking Policy
Suppose you have sensitive employee salary information that should be masked based on the user’s role. You can add a salary column to the employees table and apply a masking policy to protect this data.
Create Masking Policy
First, ensure that a masking policy is already created. Here’s an example of creating a masking policy named mask_salary:
_10CREATE MASKING POLICY mask_salary AS (val FLOAT) RETURNS FLOAT ->_10CASE_10 WHEN CURRENT_ROLE() IN ('HR_Manager', 'Payroll_Admin') THEN val_10 ELSE NULL_10END;
This policy masks the salary column values unless accessed by users with the HR_Manager or Payroll_Admin roles.
Add a Column with the Masking Policy
_10ALTER TABLE employees _10ADD COLUMN salary FLOAT _10WITH MASKING POLICY mask_salary;
This command adds the salary column to the employees table and applies the mask_salary masking policy, ensuring that salary details are secured based on the accessing user’s role.
