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, 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

References

Snowflake alter table documentation