Understanding a Data Model

#postgres #programming #sql

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