This short article will explain the difference between comments in PostgreSQL code and the COMMENT statement, and show you how to use both. It will show you how to add comments to your PostgreSQL query code, so that you can leave yourself (and others) notes to remind you what the code does, or explain any tricky queries you’ve constructed.
Comments in PostgreSQL Code
When writing the code that forms your PostgreSQL queries, you can leave comments in two ways.
The first is to use two dashes preceding the commented line to leave a single-line comment (—):
-- This is a single line PostgreSQL comment
The second is to use the /* and */ symbols to mark the beginning and end of comments, which can span multiple lines prepended with *:
/* This is a single line comment */ /* * This is a * multiline * comment */
Comments can be placed standalone on their own lines:
/* Get a list of usernames and emails from the table tbl_users */ SELECT username, email FROM tbl_users;
…Or follow at the end of a line of code:
SELECT username, email /* Get a list of usernames and emails */ FROM tbl_users; /* from the table tbl_users */
This method is fine for commenting your inline PostgreSQL queries in your code, or in the scripts that you will run when to populate your database – but be aware – These comments will not carry through to your database after the code is executed – they comment the code only. So, if you comment a file that creates a table, those comments will not appear in the database in any shape or form attached to that table, or otherwise.
The COMMENT Statement for Describing and Annotating Your Databases
If you want to annotate your database, so that the comments are included in the database structure for other users connected to, and managing, the database to see, you need to use the COMMENT statement. This is a great way to keep your database organised, make sure that everyone working on it (or your future self) knows the purpose of each table, column, function, or other part of your database.
Below, a comment is left on the table tbl_users:
COMMENT ON TABLE tbl_users IS 'This is the users table.';
You can comment on pretty much any entity in a PostgreSQL database:
COMMENT ON COLUMN tbl_users.email IS 'User email address'; COMMENT ON INDEX user_id IS 'Enforces uniqueness on users ID'; COMMENT ON PROCEDURE report_user_count () IS 'Runs a report on the current user count'; COMMENT ON FUNCTION my_function (timestamp) IS 'Returns the current timestamp';
To update a comment, overwrite it:
COMMENT ON TABLE tbl_users IS 'This comment will overwrite any existing comment on the same entity';
To remove a comment, replace it with a null value:
COMMENT ON TABLE tbl_users IS NULL;