DEV Community

HAP
HAP

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 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';
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
Indexes:
    "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)

Collapse
 
mccurcio profile image
Matt Curcio

Good to know,
Thanks