List PostgreSQL Object Comments with SQL

List PostgreSQL Object Comments with SQL

PostgreSQL allows you to attach plain-text comments to tables, columns, indexes, functions, and other database objects using the COMMENT ON command. These comments are stored in the system catalog and are visible in psql, pgAdmin, and any tool that reads pg_description. They are one of the most underused features for keeping a schema self-documenting.

This SQL query lists comments on every column across all tables in your database, showing the schema, table name, column name, and the comment text. It is useful for auditing schema documentation, onboarding new team members, and verifying that comments are in place before a schema handover.

SQL Script

 1SELECT
 2  c.table_schema,
 3  c.table_name,
 4  c.column_name,
 5  pgd.description
 6FROM
 7  pg_catalog.pg_statio_all_tables AS st
 8INNER JOIN
 9  pg_catalog.pg_description pgd
10  ON (pgd.objoid = st.relid)
11INNER JOIN
12  information_schema.columns c
13  ON (pgd.objsubid = c.ordinal_position
14  AND c.table_schema = st.schemaname
15  AND c.table_name = st.relname);

Notes: Works on all supported PostgreSQL versions. Returns one row per commented column. Columns with no comment do not appear because the join to pg_description is an INNER JOIN. Change it to a LEFT JOIN if you want to see all columns, including those without comments.

Code Breakdown

pg_catalog.pg_statio_all_tables

This view provides one row per table including both user tables and system catalog tables. Here it is used as a source of table OIDs (relid) and schema/table name pairs (schemaname, relname). Using this view is the standard way to map a table name to its OID for joining with pg_description.

pg_catalog.pg_description

The pg_description catalog stores all comments created with COMMENT ON. Key columns used here:

• objoid — the OID of the object the comment is attached to
• objsubid — for column comments, this is the column's ordinal position (1-based). For table-level comments, objsubid is 0.
• description — the comment text itself

pgd.objoid = st.relid

Matches each comment to its parent table by OID.

pgd.objsubid = c.ordinal_position

Links the comment to the specific column by its ordinal position number. PostgreSQL stores column comments against the table OID with the column's position as the sub-object identifier.

information_schema.columns

A standard SQL view listing every column in every table. The ordinal_position column is the 1-based index of the column within its table, which matches pgd.objsubid.

c.table_schema = st.schemaname AND c.table_name = st.relname

Ensures the column metadata from information_schema is matched to the correct table in the correct schema, avoiding cross-schema name collisions.

Key Points

• Only columns that have a comment defined will appear. An empty result means no column comments exist in the database.
• To see table-level comments instead of column comments, filter WHERE pgd.objsubid = 0.
• The query covers all schemas. Add WHERE c.table_schema = 'public' to limit results to one schema.
• description will be NULL if a comment was previously removed with COMMENT ON COLUMN ... IS NULL.

Insights and Explanations

Comments are added with the COMMENT ON command:

1-- Comment on a table
2COMMENT ON TABLE public.orders IS 'Records every customer order. One row per order.';
3
4-- Comment on a column
5COMMENT ON COLUMN public.orders.status IS 'Order lifecycle state: pending, confirmed, shipped, delivered, cancelled.';

To remove a comment, set it to NULL:

1COMMENT ON COLUMN public.orders.status IS NULL;

Column comments serve several practical purposes:

• Onboarding: A new developer can query this view to understand what each column means without reading application code or external documentation.
• Data dictionaries: Many BI tools and data catalog tools (such as dbt, DataHub, and Amundsen) read pg_description to populate their metadata automatically.
• Audit and compliance: Documenting sensitive columns (PII, financial data) with comments creates an in-database record of what data is stored and why.

To find tables that have no table-level comment defined:

1SELECT n.nspname AS schema_name, c.relname AS table_name
2FROM pg_class c
3JOIN pg_namespace n ON n.oid = c.relnamespace
4LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
5WHERE c.relkind = 'r'
6  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
7  AND d.description IS NULL
8ORDER BY n.nspname, c.relname;

Additional Considerations

• Permissions: Any user with SELECT access to the table can read its comments from pg_description. No special privileges are required.
• Performance: All joins are on system catalog tables and are instantaneous regardless of data volume in user tables.
• Functions and indexes: Comments on functions, indexes, sequences, and other objects are also stored in pg_description but require different joins (against pg_proc, pg_index, etc.). This query covers only table columns.
• psql shortcut: The \d+ tablename command in psql displays column comments inline with the table definition, making it the fastest way to check comments interactively.

References

• PostgreSQL: COMMENT
• PostgreSQL: pg_description
• PostgreSQL: pg_statio_all_tables
• andyatkinson/pg_scripts: list_comments.sql