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';

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)

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

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

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.

Blog

Understanding a Data Model

HAP

Join Our Newsletter. No Spam, Only the good stuff.

Related