Understanding a Data Model
Tags: postgres, programming, sql
Published: 2022-07-20 15:31:55

Or "How I learned to stop forgetting things and use comments."

One of the nifties of PostgreSQL is support for comments on objects. You can comment on tables and columns and so many more objects.

Let's try an example:

```sql
create table my_cool_table (
    id serial primary key,
    cool_rating numeric(5,2) not null default 0.0::numeric(5,2),
    label text not null,
    data jsonb not null default '{}'::jsonb
)
;

comment on table my_cool_table is 'Cool Ratings';
comment on column my_cool_table.id is 'Primary Key';
comment on column my_cool_table.cool_rating is 'Just how cool this is.';
comment on column my_cool_table.label is 'Label associated with data';
comment on column my_cool_table.data is 'The cool stuff';
```

OK, so now what?

Well, you can now display the comments when getting information. For instance, using `psql` you can \dt+ and see the table comment.

```sql
postgres=# \dt+ my_cool_table
                                  List of relations
 Schema |     Name      | Type  |  Owner   | Persistence |    Size    | Description  
--------+---------------+-------+----------+-------------+------------+--------------
 public | my_cool_table | table | postgres | permanent   | 8192 bytes | Cool Ratings
(1 row)
```

You can get the column comments using `\d+ my_cool_table`

```sql
postgres=# \d+ my_cool_table
                                                             Table "public.my_cool_table"
   Column    |     Type     | Collation | Nullable |                  Default                  | Storage  | Stats target |        Description         
-------------+--------------+-----------+----------+-------------------------------------------+----------+--------------+----------------------------
 id          | integer      |           | not null | nextval('my_cool_table_id_seq'::regclass) | plain    |              | Primary Key
 cool_rating | numeric(5,2) |           | not null | 0.0::numeric(5,2)                         | main     |              | Just how cool this is.
 label       | text         |           | not null |                                           | extended |              | Label associated with data
 data        | jsonb        |           | not null | '{}'::jsonb                               | extended |              | The cool stuff
Indexes:
    "my_cool_table_pkey" PRIMARY KEY, btree (id)
Access method: heap
```

There are other programs that can access the comments. dBeaver, for instance:

![dBeaver Community Edition](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1sl4u9mvi5ybzje0htgu.png)

There are also some documentation programs that will crawl your DB and output various format files that can include comments.

SQLAlchemy ORM should [support comments](https://github.com/sqlalchemy/alembic/issues/565) if you're using it to create database objects.

Use of the PostgreSQL comments can really help new developers but be a handy reference whenever changes are necessary.

See the [PostgreSQL COMMENT documentation](https://www.postgresql.org/docs/current/sql-comment.html) to see all of the objects on which comments are supported.