sqlsnowflake-cloud-data-platform

Remove a column comment in Snowflake


From Snowflake's documentation, it's clear how to add or overwrite a comment on a table column, but it doesn't look like you can remove a comment one without re-creating the table.

CREATE TABLE "My_Table" (
  "my_column" INT
);
ALTER TABLE "My_Table" ALTER "my_column" COMMENT 'New Comment';  -- Works
ALTER TABLE "My_Table" ALTER "my_column" COMMENT NULL; -- Fails
ALTER TABLE "My_Table" ALTER "my_column" COMMENT '';  -- Works, but prefer NULL

As shown above, the best I can find is to set the comment to '', but I would really prefer it to be null. Does anyone know how to remove a comment?


Solution

  • It is possible to remove comment with ALTER TABLE ... UNSET COMMENT clause:

    CREATE TABLE my_table (my_column INT);
    
    ALTER TABLE my_table ALTER my_column COMMENT 'New Comment'; 
    
    SELECT table_schema, table_name, column_name, comment
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ILIKE 'my_table';
    /*
    TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME COMMENT
    PUBLIC  MY_TABLE    MY_COLUMN   New Comment
    */
    
    ALTER TABLE my_table ALTER my_column UNSET COMMENT; 
    
    SELECT table_schema, table_name, column_name, comment
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ILIKE 'my_table';
    /*
    TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME COMMENT
    PUBLIC  MY_TABLE    MY_COLUMN   
    */
    

    Output:

    enter image description here