CODEWITHSUNDEEP
t-sqlsnowflake-cloud-data-platform
Stifter
Stifter

Reputation: 41

Snowflake: DROP COLUMN if exists

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!

Upvotes: 2

Views: 11140

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 176104

DROP COLUMN IF EXISTS is supported. Tested on version 7.37.1

SELECT CURRENT_VERSION();
-- 7.37.1

CREATE TABLE table1(col1 INT, col2 INT);
INSERT INTO table1(col1, col2) VALUES (1,10);

SELECT * FROM table1;
-- COL1 COL2
-- 1    10

Main query:

ALTER TABLE IF EXISTS table1 
DROP COLUMN IF EXISTS col1;
-- Statement executed successfully.

SELECT * FROM table1;
-- COL2
-- 10

Reapplying query on non-existing column:

ALTER TABLE IF EXISTS table1 
DROP COLUMN IF EXISTS col1;
-- Statement executed successfully.
-- No error even though column does not exist

ALTER TABLE - Table Column Actions

DROP COLUMN [ IF EXISTS ] col_name [ CASCADE | RESTRICT ]

Removes the specified column from the table.

If you are not sure if the column already exists, you can specify IF EXISTS when dropping the column. If the column does not exist, DROP COLUMN has no effect and does not result in an error.

Upvotes: 1

Greg Pavlik
Greg Pavlik

Reputation: 11076

Edit: Since writing this, Snowflake now supports adding a column if it does not exist or drop it if it exists.

Snowflake conditional code: adding new column(idempotent script)

This is old code that was a workaround until this syntax was supported:

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

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 176104

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;

Upvotes: 2

Related Questions