Adding comments in PostgreSQL is not as obvious as it is in MySQL. They cannot be added inline, we need to use an additional query in order to generate them.
Lets create an example table :
CREATE DATABASE test; CREATE TABLE users( user_id serial PRIMARY KEY, name varchar(100) );
For adding comments to this table we just need to run the following commands:
/* Comment for the table: */ COMMENT ON TABLE users IS 'This is the table users'; /* Comment for the column "user_id": */ COMMENT ON COLUMN users.user_id IS 'This is the user identifier number'; /* Comment for the column "name": */ COMMENT ON COLUMN users.name IS 'This is the user name';
Retrieving comments it’s a bit different for tables and columns.
Retrieving table comments
For retrieving the table comment we just need to run a query using the obj_description() function. Just adding the arguments
For instance, if I want to see the comment created for the table user I just need to run the following query:
Retrieving columns comments
Retrieving comments from the columns is a bit more tricky.
We can run this query filling the database, schema and table names:
SELECT cols.column_name, ( SELECT pg_catalog.col_description(c.oid, cols.ordinal_position::int) FROM pg_catalog.pg_class c WHERE c.oid = (SELECT cols.table_name::regclass::oid) AND c.relname = cols.table_name ) as column_comment FROM information_schema.columns cols WHERE cols.table_catalog = 'YOUR_DB_NAME' AND cols.table_schema = 'YOUR_SCHEMA_NAME' AND cols.table_name = 'YOUR_TABLE_NAME';
If we run the query for the table users created in the previous example, we should get something like this:
|user_id||This is the user identifier number|
|name||This is the user name|
These approaches are quite easy to use. But of course If you know a better way, I’ll be delighted to listen.