Add column to Snowflake table

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

Stephen Allwright
Stephen Allwright

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

Other alter table commands

The alter table command is the key to modifying the properties, columns, or constraints of a table in Snowflake. Other commands that you might want to work with are:

Rename Snowflake column
Add multiple columns to Snowflake table

References

Snowflake alter table documentation

Snowflake

Stephen Allwright Twitter

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.

Comments