Comment

Adds a comment or removes an existing comment for an existing table or column object.

COMMENT

COMMENT ON (TABLE | COLUMN) <object_name> IS (<string_literal> | NULL);

Create or remove a comment for a TABLE or COLUMN object of name object_name. The comment must be a string literal or NULL. If NULL, the comment is removed. Only super-users or owners of the object can modify comments on the object.

Column and table comments can be viewed either in the information_schema system tables, or in the result of the SHOW CREATE TABLE command run on the relevant table.

Currently comments are not supported with the CREATE TABLE command, and COMMENT ON is the canonical means to set or unset comments.

Examples

1. Create a table and add a comments to it.

CREATE TABLE employees (id INT, salary BIGINT);
-- Add a comment to the 'employees' table
COMMENT ON TABLE employees IS 'This table stores employee information';
-- Add a comment to the 'salary' column
COMMENT ON COLUMN employees.salary IS 'Stores the salary of the employee';

When specifying the name of the COLUMN object, it must be of the form <TABLE>.COLUMN> to uniquely identify it.

2. Show the comments and the DDL of the table.

SHOW CREATE TABLE employees;

CREATE TABLE employees /* This table stores employee information */ (
  id INTEGER,
  salary BIGINT /* Stores the salary of the employee */);
1 rows returned.

Currently COMMENT ON is supported only on tables and and columns of that table. Other objects such as VIEW are not currently supported.

3. View the table and column comment in respective system table.

-- Connect to information_schema database
\c information_schema admin XXXXXXXX

-- Select subset of columns from the tables system table
SELECT table_id,table_name,"comment" FROM tables where table_name = 'employees';

-- Returns one result for the table comment
table_id|table_name|comment
5|employees|This table stores employee information
1 rows returned.

-- Select subset of columns from the columns system table
SELECT table_id,table_name,column_id,column_name,"comment" FROM columns where table_name = 'employees';

-- Returns two results, one of the columns has no comment.
table_id|table_name|column_id|column_name|comment
5|employees|1|id|NULL
5|employees|2|salary|Stores the salary of the employee
2 rows returned.