Getting New and Old Columns to a Table

In my last post, we worked out how to change the data type of a field if it already existed and was found to need changing. This time, I want to add a fresh, new column to an existing table.

In SQL Server, I would be doing something like the following.

ALTER TABLE dbo.TestTable
ADD	LastName varchar(50) NULL;

This works great if no columns are named “LastName” in dbo.TestTable. If there are though, the second time this is run – whether on accident or if you have a CI/CD process that runs and reruns scripts – this will cause an error.

You will need to rewrite the query slightly to be more like the following:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TestTable' AND COLUMN_NAME = 'LastName')
ALTER TABLE dbo.TestTable
ADD	LastName varchar(50) NULL;

What do we do in Snowflake? The same as we do in SQL Server? (Hint: You can, but it’s not recommended.)

ALTER TABLE TestTable ADD COLUMN IF NOT EXISTS LastName VARCHAR(50) NULL;

Yes, that’s right. As part of the adding of the column, you can check if it already exists! No manual checks, just get ‘er done!

Now, sadly, that does come with a caveat. You cannot use the “IF NOT EXISTS” part if you are also specifying any of the following:

1. A Default for the column.
2. The column is AutoIncrementing.
3. Any constraints for the column – such as UNIQUE, PRIMARY KEY, or FOREIGN KEY.

With that, I hope you have learned something useful that you can put to use in Snowflake soon. As always, remember to check to see if your tables allow you to add a new column with the name you want.