COMMENT ON

In this section, you will find information about the transformation done to the Comment ON in PostgreSQL.

Description

COMMENT ON statement is used to add descriptive comments to database objects such as tables, columns, functions, and more.

The comment on is partially supported by Snowflake.

Click here to navigate to the PostgreSQL documentation page for this syntax.

Grammar Syntax

PostgreSQL

COMMENT ON
{
  ACCESS METHOD object_name |
  AGGREGATE aggregate_name ( aggregate_signature ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  COLUMN relation_name.column_name |
  CONSTRAINT constraint_name ON table_name |
  CONSTRAINT constraint_name ON DOMAIN domain_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  EXTENSION object_name |
  EVENT TRIGGER object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  MATERIALIZED VIEW object_name |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  POLICY policy_name ON table_name |
  [ PROCEDURAL ] LANGUAGE object_name |
  PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  PUBLICATION object_name |
  ROLE object_name |
  ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  STATISTICS object_name |
  SUBSCRIPTION object_name |
  TABLE object_name |
  TABLESPACE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TRANSFORM FOR type_name LANGUAGE lang_name |
  TRIGGER trigger_name ON table_name |
  TYPE object_name |
  VIEW object_name
} IS { string_literal | NULL }

Snowflake

COMMENT [IF EXISTS] ON <object_type> <object_name> IS '<string_literal>';

COMMENT [IF EXISTS] ON COLUMN <table_name>.<column_name> IS '<string_literal>';

Supported syntax

There are certain scenarios that are supported in Snowflake. The following syntax of PostgreSQL is supported:

COMMENT ON <OBJECT_TYPE> object_name [PARAMETERS] IS '<COMMENT>';

Where:

<OBJECT_TYPE> = [TABLE | DATABASE | FUNCTION | MATERIALIZED VIEW | PROCEDURE | ROLE | SCHEMA| VIEW | TABLE| SEQUENCE]

[PARAMETERS] = ([ argmode ] [ argname ] argtype [, ...])

The parameters section in Snowflake only supports argtype.

Examples

PostgreSQL

-- table case
 COMMENT ON TABLE mytable IS 'TEXT';
 
-- procedure case declaration only
 COMMENT ON PROCEDURE procedure_name IS 'this is a comment';
 
-- procedure case with multiple parameters
COMMENT ON PROCEDURE procedure_name(arg1 FLOAT, arg2 text) IS 'this is a comment';

-- procedure case with empty parameters
 COMMENT ON PROCEDURE procedure_name() IS 'this is a comment';

Snowflake

-- table case
 COMMENT ON TABLE mytable IS 'TEXT';
 
-- procedure case declaration only
 COMMENT ON PROCEDURE procedure_name() IS 'this is a comment';

-- procedure case with multiple parameters
COMMENT ON FUNCTION function_name (text /*** MSC-WARNING - MSC-PG0023 - THE ARGUMENT MODE(IN, OUT, INOUT, VARIADIC) ARE NOT SUPPORTED BY SNOWFLAKE. BY DEFAULT, ALL THE ARGUMENTS ARE in IN MODE ***/, integer /*** MSC-WARNING - MSC-PG0023 - THE ARGUMENT MODE(IN, OUT, INOUT, VARIADIC) ARE NOT SUPPORTED BY SNOWFLAKE. BY DEFAULT, ALL THE ARGUMENTS ARE in IN MODE ***/) IS 'this is a comment';

-- procedure case with empty parameters
 COMMENT ON PROCEDURE procedure_name() IS 'this is a comment';

Null is converted to empty string

In PostgreSQL, to remove a comment from an object, you use the same syntax, but assign 'NULL' to the comment field. In Snowflake, to perform the same process, you assign an empty string as the value. It will generate a warning in the output code MSC-PG0021.

PostgreSQL

COMMENT ON <OBJECT_TYPE> object_name IS NULL;

Snowflake

COMMENT ON <OBJECT_TYPE> object_name IS '' /*** MSC-WARNING - MSC-PG0021 - NULL IS CONVERTED TO '' AND MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/;

Unsupported syntax

Unsupported syntax will generate an EWI (MSC-PG0020) and comment on the text.

PostgreSQL

 COMMENT ON RULE rule_name on TABLE_NAME IS 'this is a comment';

Snowflake

-- --** MSC-WARNING - MSC-PG0020 - COMMENT ON 'RULE' IS NOT SUPPORTED BY SNOWFLAKE. **
-- COMMENT ON RULE rule_name on TABLE_NAME IS 'this is a comment'
                                                               ;

Related EWIs

1. MSC-PG0020: Comment on 'Type' is not supported by Snowflake.
2. MSC-PG0021: NULL is converted to '' and may have a different behavior in Snowflake.
3. MSC-PG0022: Materialized view is only supported in the enterprise version of Snowflake.

4. MSC-PG0023: The argument mode (IN, OUT, INOUT, VARIADIC) are not supported by Snowflake. By default, all the arguments are in IN mode.