Getting List of Table Comments in Postgresql

Getting list of table comments in PostgreSQL

All comments are stored in pg_description

To get the comments on a table, you need to join it to pg_class

As an alternative you can also use the function obj_description() to retrieve this information:

SELECT obj_description(oid)
FROM pg_class
WHERE relkind = 'r'

Edit

In psql you can simply use the \d+ command to show all tables including their comments. Or use the \dd command to show all comments in the system

PostgreSQL query for table column comments?

How about this:

select col_description((table_schema||'.'||table_name)::regclass::oid, ordinal_position) as column_comment
, * from information_schema.columns 
WHERE table_schema = 'schema_name' 
and table_name = 'table_name';

How to retrieve the comment of a PostgreSQL database?

To get the comment on the database, use the following query:

select description from pg_shdescription
join pg_database on objoid = pg_database.oid
where datname = '<database name>'

This query will get you table comment for the given table name:

select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
where relname = '<your table name>'

If you use the same table name in different schemas, you need to modify it a bit:

select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
join pg_namespace on pg_class.relnamespace = pg_namespace.oid
where relname = '<table name>' and nspname='<schema name>'

How to show all objects and their comments in PostgreSQL?

information_schema is defined by an ANSI standard, so it only covers objects described by the SQL spec. Indexes aren't included, nor is anything Postgres-specific (event triggers, row security policies, etc.). If you want an exhaustive list, you'll need to go to the system catalogs.

There is no central list of objects; each object type lives in its own table, so you need to query each of them separately. However, there are some handy object information functions which will accept any object type, so we can glue these queries together fairly easily with some dynamic SQL:

create function describe_all_objects()
  returns table(
    type text,
    schema text,
    name text,
    identity text,
    comment text
  )
as $$
declare
  /* Cutoff for system object OIDs; see comments in src/include/access/transam.h */
  MIN_USER_OID constant oid = 16384;
  catalog_class regclass;
begin
  for catalog_class in
    /* Get a list of all catalog tables with an OID */
    select oid::regclass
    from pg_class
    where
      relhasoids and
      pg_class.oid < MIN_USER_OID and
      /* Enum members have no obj_description(); the enum itself is picked up in pg_type */
      pg_class.oid <> 'pg_enum'::regclass
  loop
    return query execute format(
      $SQL$
        /* Get descriptions for all user-created catalog entries */
        select
          info.type,
          info.schema,
          info.name,
          info.identity,
          coalesce(
            obj_description(catalog_table.oid, catalog_table.tableoid::regclass::text),
            shobj_description(catalog_table.oid, catalog_table.tableoid::regclass::text)
          ) as comment
        from
          %s as catalog_table,
          lateral pg_identify_object(catalog_table.tableoid, catalog_table.oid, 0) as info
        where
          catalog_table.oid >= %s
      $SQL$,
      catalog_class,
      MIN_USER_OID
    );
  end loop;

  /* Handle "sub-objects" (i.e. pg_attribute) separately */
  return query
    select 
      info.type,
      info.schema,
      info.name,
      info.identity,
      col_description(attrelid, attnum) as comment
    from
      pg_attribute,
      lateral pg_identify_object('pg_class'::regclass, attrelid, attnum) as info
    where
      attrelid >= MIN_USER_OID and
      attnum >= 0 and
      not attisdropped;
end
$$
language plpgsql stable;

select * from describe_all_objects();

This should cover every object in the database, right down to the implicit table array types and the columns on the TOAST table indexes, as well as server-wide objects like databases and users, so you'll likely want to filter this down quite a bit.

A handful of catalog tables need superuser permission to access directly, but if this is an issue, you should be able to modify the queries to pull the information from some public source (e.g. pg_authid is superuser-only because it contains password information, but you can look at pg_roles instead).

Let me know if you notice any omissions (and please test this more thoroughly than I did before using it for anything important :) ).

Retrieving posts with latest 2 comments of each post in postgres

The query you want would seem to be:

select p.*, c.*
from post p left join
     (select c.*,
             row_number() over (partition by postid order by id desc) as seqnum
      from comments c
     ) c
     on p.postid = c.postid and seqnum <= 2

How to see a SQL table's comment in CLI?

 \d+ world.city

From the documentation:

The command form \d+ is identical, except that more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table, the view definition if the relation is a view, a non-default replica identity setting and the access method name if the relation has an access method.

SQL access comments in postgreSQL

Ok, after a lot of research, I have come to the conclusion that one can use the following code to get descriptions of objects in PostgreSQL:

SELECT * FROM [pg_catalog_table] AS nsp LEFT OUTER JOIN pg_description AS des ON des.objoid=nsp.oid;

The [pg_catalog_table] should be substituted by a table in the pg_catalog which contains an OID column... For instance (but not limited):
pg_type ; pg_namespace ; etc.

And use this code

SELECT * FROM pg_database AS nsp  LEFT OUTER JOIN pg_shdescription AS sdes ON sdes.objoid=nsp.oid;

for descriptions from databases.

For views is a bit more tricky as they are listed in pg_class which also lists the rest of the objects (more work to do here).

There doesn't seem to be comments for servers.

Get all comment and reply comment in single SQL

What you need is a simple UNION from the 2 tables.

select id, comment, date_created
  from comment
union 
select comment_id, content,date_created
  from comment_reply
 order by id, date_created;

As far as paging Postgres dose not have a SKIP (how does that differ than OFFSET anyway), but LIMIT and OFFSET

select id, comment, date_created
  from comment
union 
select comment_id, content,date_created
  from comment_reply
 order by id, date_created
 limit &No_of_Rows    
offset &Skip; 

Depending on your implementation you may want to wrap the query in a SQL function:

create or replace 
function show_comments_and_reply( offset_in integer  default 0 
                                , limit_in  integer  default null
                                ) 
  returns setof  comment 
 language sql
as $$
    select id, comment, created_date
      from comment
    union 
    select comment_id, content,created_date
      from comment_reply
     order by id, created_date
     limit limit_in    
    offset offset_in;
$$; 

See demo here.

---

Related Topics