merqurio
merqurio

Reputation: 941

How to list all the uncommented PostgreSQL columns?

I'm documenting the schema of a database using the PostgreSQL built-in comment capabilities. At some point I forgot to comment some columns but I'm not sure which ones.

Is there a way to list all the columns in a schema that are not commented ? That would be very helpful to be able to end commenting all the columns.

Upvotes: 0

Views: 154

Answers (1)

merqurio
merqurio

Reputation: 941

Yes, this is possible. To do so, you need to find out all the columns in the schema that are not listed in the pg_description. You can start listing all the column comments in the database:

SELECT * 
FROM pg_description
WHERE  objsubid > 0;
objoid | classoid | objsubid | description
-------|----------|----------|------------
68258  | 1259     | 10       | The number of individual Devices used in the exposure.
68258  | 1259     | 11       | A key to the provider who administered the... 

Column objsubid refers to the column order in a table, if the row object is a column, for all other object types, objsubid is zero.

Then find all the columns in a schema, by checking the informataion_schema.columns relation:

SELECT
  (cols.table_schema || '.' || cols.table_name)::regclass, 
  cols.table_name, 
  cols.column_name, 
  cols.ordinal_position
FROM 
  information_schema.columns AS cols 
WHERE 
  cols.table_schema = 'my_schema';
table_name | column_name  | ordinal_position | oid
-----------|--------------|------------------|----
concept    | concept_id   | 1                | 67630
concept    | concept_name | 2                | 67630

The ordinal_position column refers to the position of the column within the table, the same position objsubid references to. The statement (cols.table_schema || '.' || cols.table_name)::regclass creates a string representation of the table (Ex. my_schema.my_table) that points the internal Postgres identifier, which is the same identifier as objoid in the pg_description table.

Finally, doing a LEFT JOIN on pg_description results in the uncommented columns emerge. Remember to change the table_schema to your schema. Alternatively you can just ignore internal uncommented stuff with cols.table_schema != 'pg_catalog' AND cols.table_schema != 'information_schema'.

SELECT
   column_name,
   table_name
FROM (
    SELECT
        cols.table_name,
        cols.column_name,
        cols.ordinal_position,
        (cols.table_schema || '.' || cols.table_name)::regclass AS table_oid
    FROM information_schema.columns AS cols
    WHERE cols.table_schema = 'my_schema'
) AS column_table_oid
LEFT JOIN pg_description 
    ON pg_description.objsubid = column_table_oid.ordinal_position
    AND pg_description.objoid = column_table_oid.table_oid
WHERE 
    pg_description.objoid IS NULL
    AND pg_description.objsubid IS NULL;

Upvotes: 1

Related Questions