DEV Community


Posted on

Understanding a Data Model

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:

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 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 is 'The cool stuff';
Enter fullscreen mode Exit fullscreen mode

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.

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)
Enter fullscreen mode Exit fullscreen mode

You can get the column comments using \d+ my_cool_table

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
    "my_cool_table_pkey" PRIMARY KEY, btree (id)
Access method: heap
Enter fullscreen mode Exit fullscreen mode

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

dBeaver Community Edition

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 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 to see all of the objects on which comments are supported.

Top comments (1)

mccurcio profile image
Matt Curcio

Good to know,