
Add column to Snowflake table, simply explained
Adding a column to a table in Snowflake is a common and easy task to undertake by using the alter table command
Adding a column to a table in Snowflake is a common and easy task to undertake by using the alter table
command, here is the simplest example of how to add a column to a table:
alter table table_name add new_column_name number
Alter table add column in Snowflake
We can build upon the simple example we showed previously by adding an if exists
constraint, which checks first if the table exists before adding the column in the table. This is helpful as it stops potential errors being returned.
alter table if exists table_name add new_column_name number
Adding a column to Snowflake table with constrains
We always need to define the datatype of the column that we are adding, which we have shown in each example so far, but we could also apply other constraints to the column that we are adding. These constraints could be:
unique
primary key
foreign key
not null
maximum length
default value
In this example I will show how to add the common not null
constraint to a new column
alter table if exists table_name add new_column_name number not null
Adding a new column in Snowflake, things to watch out for
When adding a column, there are two things to keep in mind:
- The new column name must not be currently used in the table
- Objects (such as view definitions) that select all columns from your altered table will now fetch the new column, if this is not wanted then you will have to go and edit these objects manually
Related articles
Editing tables
Rename Snowflake column
Add multiple columns to Snowflake table
Drop columns from Snowflake table