CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Jeff Breadner
Jeff Breadner

Reputation: 1438

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?

Upvotes: 3

Views: 993

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

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

Upvotes: 1

john.da.costa
john.da.costa

Reputation: 4835

steps are:

  1. create a new column column2
  2. update the table so column2 = column1
  3. drop column1
  4. then rename column2 to column1;

Not sure if its worth the effort, but here goes:

alter table my_table add column my_column2 int;

update my_table set my_column2 = my_column;

alter table my_table drop column my_column;

alter table my_table rename column my_column2 to my_column;

describe table my_table ;

Validation of Script - Image

Upvotes: 0

Related Questions