support column comments

[neverchanje]

Is your feature request related to a problem? Please describe.

There are many databases that allows commenting on columns, either during CREATE TABLE or after table creation through commands like COMMENT ON.

During CREATE TABLE

CREATE TABLE <table_name> (v INT(11) COMMENT 'table comment')

ALTER TABLE <table_name> MODIFY v INT(11) COMMENT = "new table comment"

COMMENT ON

COMMENT ON TABLE vehicles IS 'This table contains information about vehicles registered with MovR.';

SHOW TABLES FROM movr WITH COMMENT;
-- or
select obj_description('mySchema.myFunction'::regproc, 'pg_proc');

	COMMENT ON	CREATE TABLE (COMMENT 'cmt')
Snowflake	Y	Y
Redshift	Y	N
Oracle	Y	N
MySQL	N	Y
Hive	N	Y
FlinkSQL	N	Y
DuckDB	N	N

Apparently, column comment has been widely supported by many popular SQL systems. COMMENT ON is in favor of by primarily the PG and Oracle families while big data and MySQL families prefer the other. Snowflake supports the both.

I personally prefer Snowflake's syntax that combines both conventions:

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

CREATE [ OR REPLACE ] <object_type> [ IF NOT EXISTS ] <object_name>
  [ <object_type_properties> ]
  [ <object_type_params> ]
  [ COMMENT = '<string_literal>' ]

DESC TABLE mytable;

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[xxchan]

I have a concern that probably many users will use tools like DBeaver to read/write comments, so we'd better maintain PG compatibility for this feature.

[neverchanje]

Do you mean that DBeaver may be unable to recognize RisingWave's custom sytnax? This is a valid point. To be specific, my idea is that:

• First implement COMMENT ON
• Support showing column comments in DESCRIBE https://docs.risingwave.com/docs/current/sql-describe/ and SHOW COLUMNS https://docs.risingwave.com/docs/current/sql-show-columns/
• See if there is a strong need for commenting in CREATE TABLE

[xiangjinwu]

Just adding some links:
https://www.postgresql.org/docs/16/sql-comment.html
https://www.postgresql.org/docs/16/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE
https://www.postgresql.org/docs/16/catalog-pg-description.html

The PostgreSQL doc suggests comments are not limited to columns, and they are stored separately then joined, rather than as an attribute of column.

[neverchanje]

That's right. I guess that's why COMMENT ON comes into play. Commenting on CREATE TABLE is rather limited bcs only columns can have comments.