Snowflake: DROP COLUMN if exists

Question

I didn't find any easy way to "translate" the following T-SQL query in Snowflake.

ALTER TABLE table1
DROP COLUMN if exists [col1]

Any ideas? Thanks!

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-07-06T15:06:17

There is currently no way to specify if_exists when dropping a column, so for a simple translation:

ALTER TABLE table1
drop column "col1";

If "col1" does not exist (wrapping in quotes makes it case sensitive), then it will result in a SQL compilation error. If generating an error won't work for your use case (for example code will think it's a more serious problem), you can use external logic (such as Python) or internal logic in a stored procedure to check for the column's existence before trying to drop it. You can test for a column's existence using this SQL, which will return 1 if the column exits, 0 if not:

select count(*) as COLUMN_EXISTS
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'SCHEMA_TO_CHECK' and
      TABLE_NAME   = 'TABLE_TO_CHECK' and
      COLUMN_NAME  = 'COLUMN_TO_CHECK'
;

Snowflake: DROP COLUMN if exists

1 Answers