Reputation: 941
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
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