COMMENT ON

The COMMENT ON associates comments to , , , , or .

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

The user must have the CREATE on the object they are commenting on.

Synopsis

Parameters

Parameter	Description
database_name	The name of the on which you are commenting.
schema_name	The name of the on which you are commenting.
type_name	The name of the on which you are commenting.
table_name	The name of the on which you are commenting.
column_name	The name of the on which you are commenting.
table_index_name	The name of the on which you are commenting.
comment_text	The comment () you are associating to the object. You can remove a comment by replacing the string with NULL.

Examples

Setup

To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:

$ cockroach demo

Add a comment to a database

To add a comment to a database:

> COMMENT ON DATABASE movr IS 'This database contains information about users, vehicles, and rides.';

To view database comments, use :

> SHOW DATABASES WITH COMMENT;

  database_name | owner | primary_region | regions | survival_goal |                               comment
----------------+-------+----------------+---------+---------------+-----------------------------------------------------------------------
  defaultdb     | root  | NULL           | {}      | NULL          | NULL
  movr          | demo  | NULL           | {}      | NULL          | This database contains information about users, vehicles, and rides.
  postgres      | root  | NULL           | {}      | NULL          | NULL
  system        | node  | NULL           | {}      | NULL          | NULL
(4 rows)

Add a comment to a table

To add a comment to a table:

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

To view table comments, use :

> SHOW TABLES FROM movr WITH COMMENT;

          table_name         |                               comment
+----------------------------+----------------------------------------------------------------------+
  users                      |
  vehicles                   | This table contains information about vehicles registered with MovR.
  rides                      |
  vehicle_location_histories |
  promo_codes                |
  user_promo_codes           |
(6 rows)

You can also view comments on a table with :

> SHOW CREATE TABLE vehicles;

  table_name |                                          create_statement
-------------+------------------------------------------------------------------------------------------------------
  vehicles   | CREATE TABLE vehicles (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     type VARCHAR NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     status VARCHAR NULL,
             |     current_location VARCHAR NULL,
             |     ext JSONB NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id),
             |     INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
             | );
             | COMMENT ON TABLE vehicles IS 'This table contains information about vehicles registered with MovR.'
(1 row)

Add a comment to a column

To add a comment to a column:

> COMMENT ON COLUMN users.credit_card IS 'This column contains user payment information.';

To view column comments, use :

> SHOW COLUMNS FROM users WITH COMMENT;

  column_name | data_type | is_nullable | column_default | generation_expression |   indices    | is_hidden |                    comment
--------------+-----------+-------------+----------------+-----------------------+--------------+-----------+-------------------------------------------------
  id          | UUID      |      f      | NULL           |                       | {users_pkey} |     f     | NULL
  city        | VARCHAR   |      f      | NULL           |                       | {users_pkey} |     f     | NULL
  name        | VARCHAR   |      t      | NULL           |                       | {users_pkey} |     f     | NULL
  address     | VARCHAR   |      t      | NULL           |                       | {users_pkey} |     f     | NULL
  credit_card | VARCHAR   |      t      | NULL           |                       | {users_pkey} |     f     | This column contains user payment information.
(5 rows)

Add a comment to an index

Suppose we on the name column of the users table:

> CREATE INDEX ON users(name);

To add a comment to the index:

> COMMENT ON INDEX users_name_idx IS 'This index improves performance on queries that filter by name.';

To view column comments, use :

> SHOW INDEXES FROM users WITH COMMENT;

  table_name |   index_name   | non_unique | seq_in_index | column_name | direction | storing | implicit | visible |                             comment
-------------+----------------+------------+--------------+-------------+-----------+---------+----------+---------+------------------------------------------------------------------
  users      | users_name_idx |     t      |            1 | name        | ASC       |    f    |    f     |    t    | This index improves performance on queries that filter by name.
  users      | users_name_idx |     t      |            2 | city        | ASC       |    f    |    t     |    t    | This index improves performance on queries that filter by name.
  users      | users_name_idx |     t      |            3 | id          | ASC       |    f    |    t     |    t    | This index improves performance on queries that filter by name.
  users      | users_pkey     |     f      |            1 | city        | ASC       |    f    |    f     |    t    | NULL
  users      | users_pkey     |     f      |            2 | id          | ASC       |    f    |    f     |    t    | NULL
  users      | users_pkey     |     f      |            3 | name        | N/A       |    t    |    f     |    t    | NULL
  users      | users_pkey     |     f      |            4 | address     | N/A       |    t    |    f     |    t    | NULL
  users      | users_pkey     |     f      |            5 | credit_card | N/A       |    t    |    f     |    t    | NULL
(8 rows)

Add a comment to a type

Issue a SQL statement to :

CREATE TYPE IF NOT EXISTS my_point AS (x FLOAT, y FLOAT, z FLOAT);

To view the type you just created, use :

SHOW TYPES;

  schema |   name   | owner
---------+----------+--------
  public | my_point | root
(1 row)

To add a comment on the type, use a statement like the following:

COMMENT ON TYPE my_point IS '3D point';

To view all comments on types, make a against the system.comments table:

SELECT * FROM system.comments;

  type | object_id | sub_id | comment
-------+-----------+--------+-----------
     7 |       112 |      0 | 3D POINT
(1 row)

Remove a comment from a database

To remove a comment from a database:

> COMMENT ON DATABASE movr IS NULL;

> SHOW DATABASES WITH COMMENT;

  database_name | owner | primary_region | regions | survival_goal | comment
----------------+-------+----------------+---------+---------------+----------
  defaultdb     | root  | NULL           | {}      | NULL          | NULL
  movr          | demo  | NULL           | {}      | NULL          | NULL
  postgres      | root  | NULL           | {}      | NULL          | NULL
  system        | node  | NULL           | {}      | NULL          | NULL
(4 rows)

Remove a comment from a type

To remove the comment from the type you created in the preceding example, add a NULL comment:

COMMENT ON TYPE my_point IS NULL;

Known limitations

• The statement associates comments to databases, tables, or columns. However, the internal table (system.comments) in which these comments are stored is not captured by a of an individual table or database. As a workaround, take a cluster backup instead, as the system.comments table is included in cluster backups.

See also