COMMENT ON

On this page

The COMMENT ON statement associates comments to databases, tables, columns, or indexes.

Note:

The COMMENT ON statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Required privileges

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

Synopsis

Parameters

Parameter	Description
`database_name`	The name of the database on which you are commenting.
`schema_name`	New in v21.2: The name of the schema on which you are commenting.
`table_name`	The name of the table on which you are commenting.
`column_name`	The name of the column on which you are commenting.
`table_index_name`	The name of the index on which you are commenting.
`comment_text`	The comment (`STRING`) you are associating to the object. You can remove a comment by replacing the string with `NULL`.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr 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:

> 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:

> 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:

> 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:

> SHOW COLUMNS FROM users WITH COMMENT;

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

Add a comment to an index

Suppose we create an index 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 ... WITH COMMENT:

> SHOW INDEXES FROM users WITH COMMENT;

  table_name |   index_name   | non_unique | seq_in_index | column_name | direction | storing | implicit |                             comment
-------------+----------------+------------+--------------+-------------+-----------+---------+----------+------------------------------------------------------------------
  users      | primary        |   false    |            1 | city        | ASC       |  false  |  false   | NULL
  users      | primary        |   false    |            2 | id          | ASC       |  false  |  false   | NULL
  users      | primary        |   false    |            3 | name        | N/A       |  true   |  false   | NULL
  users      | primary        |   false    |            4 | address     | N/A       |  true   |  false   | NULL
  users      | primary        |   false    |            5 | credit_card | N/A       |  true   |  false   | NULL
  users      | users_name_idx |    true    |            1 | name        | ASC       |  false  |  false   | This index improves performance on queries that filter by name.
  users      | users_name_idx |    true    |            2 | city        | ASC       |  false  |   true   | This index improves performance on queries that filter by name.
  users      | users_name_idx |    true    |            3 | id          | ASC       |  false  |   true   | This index improves performance on queries that filter by name.

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)

Pricing

Contact us

Sign In