mysqlやpostgresにおける tableやcolumnへのコメント追加と確認

コメント関連の操作は、DBMS製品毎に異なる為、以下にメモ

mysql の場合

まずは、テーブル作成

create table test_tbl (
id         int primary key comment 'これはtest_tbl.idのcomment',
val        varchar(256)
)
comment='これはtest_tblのcomment';

上記で作成したコメントは、通常?の desc では表示されない為、 "show table status like ～" または "show create table ～"を使用します。

mysql> desc test_tbl;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   | PRI | NULL    |       |
| val   | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

mysql>  show table status like 'test_tbl';
+----------+--------+～+---------------------+～+--------------------------------------+
| Name     | Engine |～| Create_time         |～| Comment                              |
+----------+--------+～+---------------------+～+--------------------------------------+
| test_tbl | InnoDB |～| 2021-01-31 10:15:41 |～| これはtest_tblのcomment              |
+----------+--------+～+---------------------+～+--------------------------------------+

mysql> show full columns from test_tbl;
+-------+--------------+--------------------+------+～+--------------------------------+
| Field | Type         | Collation          | Null |～| Comment                        |
+-------+--------------+--------------------+------+～+--------------------------------+
| id    | int          | NULL               | NO   |～| これはtest_tbl.idのcomment     |
| val   | varchar(256) | utf8mb4_0900_ai_ci | YES  |～|                                |
+-------+--------------+--------------------+------+～+--------------------------------+

mysql>  show create table test_tbl;
+----------+---------------------------------------------------------------------------+
| Table    | Create Table                                                              |
+----------+---------------------------------------------------------------------------+
| test_tbl | CREATE TABLE `test_tbl` (                                                 |
|          |  `id`  int NOT NULL COMMENT 'これはtest_tbl.idのcomment',                 |
|          |  `val` varchar(256) DEFAULT NULL,                                         |
|          |   PRIMARY KEY (`id`)  )                                                   |
|          |   ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci        |
|      |   COMMENT='これはtest_tblのcomment'                                       |
+----------+---------------------------------------------------------------------------+

postgres の場合

まずは、テーブル作成ですが、mysqlと異なり、 "create table"とは別に"COMMENT ON ～"を実施する必要があります

create table test_tbl (
id         int primary key,
val        varchar(256)
);

COMMENT ON TABLE  test_tbl    IS 'これはtest_tblのcomment';
COMMENT ON COLUMN test_tbl.id IS 'これはtest_tbl.idのcomment';

次に "\dt+", "\d+"などで作成したコメントを表示します。

postgres=> \dt+ test_tbl
                           List of relations
 Schema |   Name   | Type  | Owner  |  Size   |       Description       
--------+----------+-------+--------+---------+-------------------------
 public | test_tbl | table | bbuser | 0 bytes | これはtest_tblのcomment

postgres=> \d+ test_tbl
          Table "public.test_tbl"
 Column |          Type          |～| Storage  |～|        Description         
--------+------------------------+～+----------+～+----------------------------
 id     | integer                |～| plain    |～| これはtest_tbl.idのcomment
 val    | character varying(256) |～| extended |～| 
Indexes:
    "test_tbl_pkey" PRIMARY KEY, btree (id)
Access method: heap

postgres=> select pg_stat_user_tables.relname as TABLE_NAME,
postgres->        pg_description.description  as TABLE_COMMENT
postgres-> from   pg_stat_user_tables,
postgres->        pg_description
postgres-> where  pg_stat_user_tables.relname in
postgres->          (select relname as TABLE_NAME
postgres(>           from   pg_stat_user_tables) and
postgres->         pg_stat_user_tables.relid=pg_description.objoid and
postgres->         pg_description.objsubid=0;

 table_name |      table_comment      
------------+-------------------------
 test_tbl   | これはtest_tblのcomment