Adding and retrieving comments on PostgreSQL tables

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.

Creating comments

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

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 'YOUR_SCHEMA.YOUR_TABLE_NAME':regclassschema.
For instance, if I want to see the comment created for the table user I just need to run the following query:

select obj_description('public.user'::regclass);

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:

column_name column_comment
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.

Comments 2

  1. Improving on the answer given,

    SELECT
    cols.column_name,
    pg_catalog.col_description(c.oid, cols.ordinal_position::int)
    FROM pg_catalog.pg_class c, 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’ AND
    cols.table_name = c.relname

    will work too.

  2. Se podría usar algo como :

    * Para una tabla específica:

    select description from pg_description
    join pg_class on pg_description.objoid = pg_class.oid
    where relname = ”;

    O en el caso de necesitar de todas la tablas de la base de datos podría ser algo así…

    select relname,description from pg_description
    join pg_class on pg_description.objoid = pg_class.oid
    where relname in (
    select table_name from information_schema.tables where
    table_catalog=’creditos1′ and table_schema not like ‘pg_%’ and
    table_schema’information_schema’ and table_type=’BASE TABLE’
    );

    Es solo una idea más….

Leave a Reply to SashiS Cancel reply

Your email address will not be published. Required fields are marked *