Snowflake ALTER SQL is throwing an error when adding column with DEFAULT value along with IF NOT EXISTS

Question

I am trying to run an ALTER query in Snowflake to add a column TEST_COL, if it does not exists already in the table TEST_SALES.

I would like to set a DEFAULT value of 'N' for the column as well.

This is the query I used:

ALTER TABLE TEST_SALES
    ADD COLUMN IF NOT EXISTS TEST_COL VARCHAR(1) DEFAULT 'N';

On rerunning the same query, it throws this error:

SQL Error [2028] [42601]: SQL compilation error: ambiguous column name 'TEST_COL'

Is there a solution?

Please note that it allows me to rerun any number of times, if I do not set the default value

Query used is:

ALTER TABLE TEST_SALES
    ADD COLUMN IF NOT EXISTS TEST_COL VARCHAR(1);

Answer 1

DEFAULT is not allowed with IF NOT EXISTS when adding a column :

From Docs :

You cannot specify IF NOT EXISTS if you are also specifying any of the following for the new column:

DEFAULT, AUTOINCREMENT, or IDENTITY

UNIQUE, PRIMARY KEY, or FOREIGN KEY

Answer 2

It seems that IF NOT EXISTS can be combined with DEFAULT (but it is ignored - based on observations v7.41.0):

CREATE OR REPLACE TABLE TEST_SALES(i INT) AS SELECT 1;

ALTER TABLE TEST_SALES
    ADD COLUMN IF NOT EXISTS TEST_COL VARCHAR(1) DEFAULT 'N';
-- Statement executed successfully.

SELECT * FROM TEST_SALES;
-- I    TEST_COL
-- 1    N

Second run:

ALTER TABLE TEST_SALES
ADD COLUMN IF NOT EXISTS TEST_COL VARCHAR(1) DEFAULT 'N';
-- ambiguous column name 'TEST_COL'

---

Workaround using Snowflake Script Block:

BEGIN
  IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
             WHERE COLUMN_NAME = 'TEST_COL'
               AND TABLE_NAME = 'TEST_SALES' 
               AND TABLE_SCHEMA = 'PUBLIC')) THEN
        ALTER TABLE TEST_SALES ADD COLUMN TEST_COL VARCHAR(1) DEFAULT 'N';
   END IF;
END;

SELECT * FROM TEST_SALES;