On this page 
Warning:
As of June 5, 2024, CockroachDB v22.2 is no longer supported. For more details, refer to the Release Support Policy.
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 |
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 | 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 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 | 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)
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)
See also
CREATE DATABASECREATE TABLEADD COLUMNCREATE INDEXSHOW TABLES- SQL Statements
- dBeaver
- Online Schema Changes
Was this helpful?
