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!

Answer 1

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'
;

Answer 2

With introduction of Snowflake Scripting and branching constructs ,such script is possible:

-- Snowsight
BEGIN
  IF (EXISTS(SELECT * 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_NAME = 'TABLE1' 
                   AND TABLE_SCHEMA = 'PUBLIC'
                   AND COLUMN_NAME = 'COL1')) THEN
    ALTER TABLE IF EXISTS tab DROP COLUMN col1;
  END IF;
END;