In PostgreSQL, we can use the comments to document the SQL scripts or various database objects such as tables, views, functions, triggers, etc. This makes it easier for developers and administrators to understand what each object entails and how to manage them.
This tutorial explores the various types of comments in the PostgreSQL server and discusses how we can create and view them.
PostgreSQL Single Line Comments
The most common type of comment is a single-line comment. Single-line comments allow us to add short descriptions or annotations to an SQL script. A single-line comment can only span until the end of the line.
Once the PostgreSQL server encounters a single-line comment, it ignores it and omits it in the execution.
In PostgreSQL, we can create the single-line comments using a double-dash symbol followed by the comment text as shown in the following:
The following shows a valid single-line comment in an SQL statement:
create table employees(
id serial primary key,
name varchar(100) not null,
age int,
department varchar(100)
);
Once PostgreSQL encounters the previous query, it ignores the first line.
We can use the single-line comments to prevent SQL from executing a specific block.
PostgreSQL Multi-Line Comments
The second common type of comment is known as multi-line comments. Unlike a single-line comment, a multi-line comment can span multiple lines which allow you to add more detailed explanations.
In PostgreSQL, we can create multi-line comments by enclosing the comment contents using the following symbols /* */
Anything between the given symbols is considered a comment and is ignored by the PostgreSQL server.
The following shows an example of a multi-line comment in PostgreSQL:
This query creates a table named "employees" with the following columns:
"id" is a serial primary key, automatically generating unique IDs.
"name" as a varchar(100) column, which stores the name of the employee and cannot be null.
"age" as an integer column, which stores the age of the employee.
"department" as a varchar(100) column, which stores the department of the employee.
This table is designed to store employee information within the database.
*/
create table employees(
id serial primary key,
name varchar(100) not null,
age int,
department varchar(100)
);
As you can see, multi-line comments allow us to give a detailed information about a query.
PostgreSQL Comment on Database Objects
PostgreSQL allows us to create comments to various database objects besides single-line and multi-line comments.
These are incredibly useful as they are stored as part of the database object which allows us to query the comment.
In PostgreSQL, we can use the COMMENT clause to add a comment to a database object. The syntax is as follows:
For example, suppose we have a table called “users.” We can add a comment to the table as shown in the following:
This should add a comment to the “users” table.
Viewing the Object Comments
To view the comment information, we can run the following query:
The given query should query the system’s catalog table and fetch the comment that is associated with the specified database object.
We can also use the “\d” command in the psql shell.
Editing the Object Comments
We can update an existing comment using the ALTER TABLE or ALTER FUNCTION clause. For example, to update the comment in the “users” table, we can run the following:
Removing the Object Comments
To remove a comment from a given database object, we can set its value to NULL as demonstrated in the following:
For example, to remove the comment from the “users” table, we can run the following:
There you have it!
Conclusion
This tutorial explores the various types of comments in the PostgreSQL server and their role. We also covered the PostgreSQL COMMENT command to add comments to different database objects.
