Why and how to write comments on database, tables, and other objects on most DBMSs. How to read comments.
Why are comments important?
It is very hard to make sense of data if we can’t understand what is stored in the various databases, tables, columns, and what other objects represent.
We need to understand which information is stored, and sometimes we also need to understand how it is represented. For example, a length column could be expressed in centimetres, meters, or feet. If city is 'London', must it actually be London or could it be a nearby village? Does address include a postcode? Should postcode be uppercase?
Whenever these details are not obvious, they should be noted in a comment. Even if they seem obvious now, they could be less obvious in one year, or for a new colleague. If a comment is not available, we’ll need to look into an internal knowledge base, or application code, or ping a team mate. Not exactly a way to boost our productivity.
Reading a comment
The syntax to read comments is the following:
COMMENT ON TABLE tab;
COMMENT ON COLUMN tab.col;
COMMENT ON VIEW vw;…and so on. This syntax works on most DBMSs – see below.
Writing a comment
To write or change a comment:
COMMENT ON TABLE tab IS 'This table is nice';
COMMENT ON COLUMN tab.col IS 'What a wonderful column!';
COMMENT ON VIEW vw IS 'I love the view from my window';It is also possible to delete a comment without writing a new one:
COMMENT ON TABLE tab IS NULL;The COMMENT ON syntax is supported by PostgreSQL, Firebird, Oracle, Db2, Redshift, Snowflake.
SQLite and Informix do not support comments on database objects.
Reference
More articles: