Transcript

Comments store information about a database object. Just like comments in code, the can be carefully used to convey intent to future developers.

There is no comment in the SQL standard, so enjoy yet another benefit of working with Postgres.

Let's create a table to demonstrate:

-- create cmbr table
create table cmbr (
  id serial primary key,
  name varchar
);

What is a 'cmbr', you ask? It's a cast member, of course, for the television show this database helps manage. It's poorly named, but this a legacy database and multiple tenants rely on that table name to function. For the purposes of this exercise, let's assume we can't rename the table.

We can, however, add a comment:

-- add comment
comment on table cmbr is 'Cast members of The Girly Show';

We can see the comment with the following command:

-- show table with comment
\dt+ cmbr;

This command tells psql to show the table with disk size and description.

We can comment on pretty much anything in Postgres, including tables, columns, and schemas. I counted 36 things we can comment on at the time of this screencast.

Only one comment is allowed per object, so to modify the comment, issue a new one to the same object.

-- replace comment
comment on table cmbr is 'Cast members of TGS with Tracy Jordan';

To remove a comment, write NULL in place of the text string. Comments are automatically dropped when their object is dropped.

-- remove comment
comment on table cmbr is null;

I used this technique recently on a data migration. We wanted to preserve a legacy table as an hstore column, but also make it clear that the new column was not meant to be written to. The comment allowed us to convey this information to future developers.

We try at Hashrocket to use comments sparingly, because they can diverge from the code or data objects they belong to. Use your best judgement when implementing this feature.

Thanks for watching.

Resources

https://www.postgresql.org/docs/current/static/sql-comment.html