PostgreSQL comes with a command - COMMENT . This command is useful if you want to record content in the database. This article will describe how to use this command.
As databases continue to evolve and data relationships become more complex, it becomes very difficult to track everything added to the database. To record how the data is organized and components that may be added or changed over time, it is necessary to add some kind of document.
For example, documents can be written in external files, but this creates a problem that they quickly become obsolete files. PostgreSQL has a solution to this problem: COMMENT command. Use it to add comments to various database objects, such as columns, indexes, tables, and functions that are updated when needed.
View data and add comments
PostgreSQL's psql interactive shell contains many powerful commands to view and manipulate data. The \d command displays a list of all visible tables, views, materialized views, sequences, and external tables. There are several combinations of \d commands that can be used to specify whether to view indexes, mappings, constraints, etc. Combined with + (e.g. \d+), the command will give you an extended view of the object, containing a description column, where the document or COMMENT is written.
The COMMENT command is the method we add data description to a database object. Don't confuse COMMENT with \**\ or -- in SQL, because they are written in SQL files and are not visible in the database. COMMENT, on the other hand, is not standard SQL, but unique to PostgreSQL.
There are many database objects for us to use the COMMENT command. The most common of these are tables, indexes, and columns. However, you must be the owner or administrator of the object to use COMMENT.
Run \d+ to display the table and its description, for example:
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+---------------+----------+------------+---------------
public | commenttest | table | postgres | 8192 bytes |
Since commenttest is a new table just created, the Description column is empty. You can add comments via the following command:
postgres=# COMMENT ON TABLE commenttest IS 'A table of students in different departments'; COMMENT
Now run \d+ again and you can see that the description column is filled with comments.
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+---------------+----------+------------+---------------
public | commenttest | table | postgres | 8192 bytes | A table of students in different departments
This is the step to add description information to the table. Next, we need to consider how to add descriptions to the columns of the table.
To view the description columns for each column in the table, you can run a command like the following:
postgres=# \d+ commenttest
Table ""
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+---------+-----------+----------+---------+----------+--------------+-------------
student_id | integer | | | | plain | |
student_name | text | | | | extended | |
student_major | text | | | | extended | |
department_id | integer | | | | plain | |
department_name | text | | | | extended | |
nationality | text | | | | extended | |
Adding a description for each column is similar to how we add a column in the table. For example:
postgres=# COMMENT ON COLUMN commenttest.student_id IS 'ID of the student'; COMMENT postgres=# COMMENT ON COLUMN commenttest.student_name IS 'name of the student'; COMMENT postgres=# COMMENT ON COLUMN commenttest.student_major IS 'major of the student'; COMMENT postgres=# COMMENT ON COLUMN commenttest.department_id IS 'ID of the department'; COMMENT postgres=# COMMENT ON COLUMN commenttest.department_name IS 'name of the department'; COMMENT postgres=# COMMENT ON COLUMN IS 'nationality of the student'; COMMENT
After adding a description, check the table's description column information again:
postgres=# \d+ commenttest
Table ""
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+---------+-----------+----------+---------+----------+--------------+----------------------------
student_id | integer | | | | plain | | ID of the student
student_name | text | | | | extended | | name of the student
student_major | text | | | | extended | | major of the student
department_id | integer | | | | plain | | ID of the department
department_name | text | | | | extended | | name of the department
nationality | text | | | | extended | | nationality of the student
You can see that the description column has been added with corresponding comments. After adding comments, the column names with complex and difficult names can make it easier for the end user to understand without ambiguity.
We can also add descriptions to the index in a similar way, so that during the database use, we can prevent confusion and ambiguity caused by the increase in the number of indexes.
And if you use pg_dump to migrate the PostgreSQL database, any comments made with COMMENT will be stored in the dump file.
Supplement: Add comments to the tables and columns of the postgresql database (comment)
There are not many people using postgresql database in China, and some old projects use this database. Maintenance is particularly troublesome because there are fewer people in China and there is very little relevant information.
There are also some functions, which are not provided by postgresql. Also, for table partitioning, the lower version of postgresql database does not have this function at all and is not supported. You need to create tables automatically for partitioning.
In short, the postgresql database is too troublesome to use. This article summarizes some methods to add comments to the tables and columns of the postgresql database, which is convenient for programmers who have already adopted the postgresql database and have to use it.
First, add comments to the table:
comment on table xttblog is 'Amateur Grass';
where xttblog is the table name and the comment added is "Amateur Grass".
The method of adding comments to a column is as follows:
create table xttblog(id int not null, url_id int); comment on column is 'Primary key ID, self-increase';
Note that when creating a table, you cannot add comment after the column. Execution will report an error after adding it, because this is the usage of MySQL and Oracle, not the usage of Postgresql.
Let’s talk about how to query the comments in the table. The sql statement is as follows:
select description from pg_descriptionjoin pg_class on pg_description.objoid = pg_class.oid where relname = 'xttblog'
The tables starting with pg_ are all system tables of the Postgresql database. A lot of information related to tables and configurations are stored in the system table.
PostgreSQL obtains the annotation information of the data table and the annotation information of the fields in the table are similar to the SQL above.
The information related to the table is in the pg_description table. Look for the pg_description system table, which contains notes for the table and fields.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.