Snowflake Supports IF [NOT] EXISTS at Column Level

Photo by Glenn Carstens-Peters on Unsplash

For database administrators and developers, adding or dropping columns are routine operations. But there are situations where you’d want to add a column only if it doesn’t already exist, or drop a column only if it already exists. This is why Snowflake extends the “IF [NOT] EXISTS” clause to the column level.

The “ALTER TABLE ADD COLUMN IF NOT EXISTS” statement lets you add a column to a table without worrying if the column is already present. This ensures the operation doesn’t return an error, and the column is added just once, if it doesn’t exist. The same logic can also be applied when attempting to drop columns with the “ALTER TABLE DROP COLUMN IF EXISTS” statement.

Example Usage

To add a new column only if it does not exist:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name data_type;

To drop an existing column only if it’s present:

ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;

It’s worth noting that these commands are idempotent. This means they can be executed multiple times without changing the result , making them especially useful for scripting or application building.

A Word of Caution

Please avoid using the “ALTER TABLE ADD COLUMN IF NOT EXISTS” statement when you are also specifying any of the following for the new column: DEFAULT, AUTOINCREMENT, IDENTITY, UNIQUE, PRIMARY KEY, or FOREIGN KEY.

For more information on this new feature, please visit: ALTER TABLE | Snowflake Documentation.