The list of all the queries

Comments of non-derived tables

Query goal: Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information.
Notes about the query: The query does not find comments of objects that are added to a database as a part of an extension.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

WITH named_objects AS (

SELECT 
c.oid,
n.nspname AS schema_name, 
c.relname AS table_name,
c.relname AS commented_object_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='f' THEN 'FOREIGN TABLE' 
WHEN c.relkind='p' THEN 'PARTITIONED TABLE' END AS commented_object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind IN ('r','f','p')

UNION SELECT
o.oid,
n.nspname AS schema_name,
c.relname AS table_name,
conname AS commented_object_name,
CASE WHEN o.contype='p' THEN 'PRIMARY KEY'
WHEN o.contype='u' THEN 'UNIQUE'
WHEN o.contype='f' THEN 'FOREIGN KEY'
WHEN o.contype='c' THEN 'TABLE CHECK'
WHEN o.contype='x' THEN 'EXCLUDE'
WHEN o.contype='t' THEN 'CONSTRAINT TRIGGER' END AS commented_object_type
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','f','p')

UNION SELECT 
tr.oid,
n.nspname AS schema_name,
c.relname AS table_name,
tr.tgname AS commented_object_name,
'TRIGGER' AS commented_object_type
FROM pg_trigger tr INNER JOIN pg_class c ON tr.tgrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND tr.tgisinternal='f'
AND c.relkind IN ('r','f','p')

UNION SELECT 
r.oid,
n.nspname AS schema_name,
c.relname AS table_name,
r.rulename AS commented_object_name,
CASE WHEN is_instead=TRUE THEN 'INSTEAD OF ' END ||
CASE WHEN ev_type='1' THEN 'SELECT'
WHEN ev_type='2' THEN 'UPDATE'
WHEN ev_type='3' THEN 'INSERT'
WHEN ev_type='4' THEN 'DELETE' END || ' RULE' AS commented_object_type
FROM pg_rewrite r INNER JOIN pg_class c ON r.ev_class=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND c.relkind IN ('r','f','p')

UNION SELECT 
c.oid,
n.nspname AS schema_name, 
c2.relname AS table_name,
c.relname AS commented_object_name,
'INDEX' AS commented_object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
INNER JOIN pg_index i ON c.oid=i.indexrelid
INNER JOIN pg_class AS c2 ON i.indrelid=c2.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND c.relkind IN ('i','I')
AND c2.relkind IN ('r','f','p')

),
named_user_defined_objects AS (
SELECT oid, schema_name, table_name, commented_object_name, commented_object_type
FROM named_objects nob
WHERE NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=nob.oid)
),
columns AS (SELECT 
c.oid,
n.nspname AS schema_name,
c.relname AS table_name,
at.attname AS commented_object_name,
'COLUMN' AS commented_object_type,
attnum AS ordinal_position
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','f','p')
AND at.attisdropped='f'
AND at.attnum>0
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=c.oid))

SELECT schema_name, table_name, commented_object_name, commented_object_type, description AS comment
FROM named_user_defined_objects AS n INNER JOIN pg_catalog.pg_description AS d ON n.oid=d.objoid
WHERE objsubid=0
UNION SELECT schema_name, table_name, commented_object_name, commented_object_type, description AS comment
FROM columns AS c INNER JOIN pg_catalog.pg_description AS d ON c.oid=d.objoid AND c.ordinal_position=d.objsubid
ORDER BY schema_name, table_name, commented_object_type, commented_object_name;

Collections where the query belongs to

Collection nameCollection description
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Lexicon bad smells and linguistic antipatternsQueries made to find the occurrences of lexicon bad smells and linguistic antipatterns

Categories where the query belongs to

Category nameCategory description
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
CommentsQueries of this category provide information about comments to the database objects that have been registered in the system catalog.

The list of all the queries