List PostgreSQL Object Comments with SQL

List PostgreSQL Object Comments with SQL

pg_description is where PostgreSQL keeps every comment attached with COMMENT ON, whether the target is a table, a column, an index, or a function. Psql shows these comments, and so does any tool that reads the catalog, which makes them a cheap way to keep a schema self-documenting.

This SQL query lists the comments on columns of ordinary tables in your database, showing the schema, table name, column name, and the comment text. Partitioned tables, foreign tables, and views are not covered. 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 of an ordinary table. Columns with no comment do not appear because the join to pg_description is an INNER JOIN. Changing that join to a LEFT JOIN is not enough to list uncommented columns, because the later join on pgd.objsubid drops the rows with no comment. To list every column, start from information_schema.columns instead:

 1SELECT
 2  c.table_schema,
 3  c.table_name,
 4  c.column_name,
 5  pgd.description
 6FROM
 7  information_schema.columns c
 8JOIN
 9  pg_catalog.pg_class cl
10  ON (cl.relname = c.table_name
11  AND cl.relnamespace = c.table_schema::regnamespace)
12LEFT JOIN
13  pg_catalog.pg_description pgd
14  ON (pgd.objoid = cl.oid
15  AND pgd.classoid = 'pg_class'::regclass
16  AND pgd.objsubid = c.ordinal_position)
17WHERE
18  c.table_schema NOT IN ('pg_catalog', 'information_schema');

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). It is a convenient way to get the OID for joining with pg_description; the second query below uses pg_class directly, which is the more usual route.

pg_catalog.pg_description

The pg_description catalog stores the comments created with COMMENT ON for objects in the current database. Comments on shared objects such as databases, roles, and tablespaces are stored cluster-wide in pg_shdescription instead. 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 the columns of tables, views, and foreign tables that the current user can access. The ordinal_position column is the 1-based position of the column within its table. PostgreSQL defines it as the column's attnum, which is the same number stored in pgd.objsubid, so the two match even when dropped columns leave gaps.

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 does not prove that no column comments exist: comments on columns of partitioned tables, foreign tables, and views are not covered, and information_schema.columns hides columns the current user cannot access.
• Table-level comments (objsubid = 0) never match this join, because ordinal_position starts at 1. Use the pg_class query in the next section to list them.
• The query covers all schemas. Add WHERE c.table_schema = 'public' to limit results to one schema.
• A comment removed with COMMENT ON COLUMN ... IS NULL is gone, so that column simply drops out of these results.

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: Because comments live in the catalog, data catalog and documentation tools can read them from pg_description rather than from a separate document.
• Audit and compliance: Documenting sensitive columns (PII, financial data) with comments creates an in-database record of what data is stored and why.

To list table-level comments:

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

To find ordinary tables that have no table-level comment defined (partitioned and foreign tables are not listed, because of the relkind = 'r' filter):

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: There is no security mechanism for viewing comments: any user connected to a database can see all the comments for objects in that database. The query also reads information_schema.columns, which shows only columns the current user owns or has some privilege on, so a restricted role can see fewer rows.
• Performance: All joins are on system catalogs, so run time depends on the number of objects in the database, not on the amount of data in user tables.
• Functions and indexes: Comments on functions, indexes, sequences, and other objects are also stored in pg_description, keyed by the OID of the object in its own catalog (pg_proc for functions, pg_class for indexes and sequences). They need different joins, and this query covers only table columns.
• psql shortcut: The \d+ tablename command in psql displays column comments inline with the table definition, which is a quick way to check comments interactively.

References

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