The list of all the queries

Comments of schemas, sequences, types, domains, domain constraints, and event triggers

Query goal: Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers.
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 
 n.oid,
 ''::text AS container_name, 
'DATABASE' AS container_type,
n.nspname AS object_name,
'SCHEMA' AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname<>'public' AND a.rolname<>'postgres')

UNION SELECT 
c.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
c.relname AS object_name,
CASE WHEN c.relkind='S' THEN 'SEQUENCE GENERATOR'
WHEN c.relkind='c' THEN 'COMPOSITE TYPE'
END AS 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 ('c','S')

UNION ALL SELECT 
pt.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
pt.typname AS object_name,
CASE WHEN pt.typtype='d' THEN 'DOMAIN'
WHEN pt.typtype='e' THEN 'ENUMERATION TYPE'
WHEN pt.typtype='r' THEN 'RANGE TYPE' END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND pt.typtype NOT IN ('b','c')

UNION SELECT
o.oid,
n.nspname || '.' || t.typname AS container_name,
'DOMAIN' AS container_type,
conname AS object_name,
'DOMAIN CHECK CONSTRAINT' AS object_type
FROM pg_constraint o INNER JOIN pg_type t ON o.contypid=t.oid
INNER JOIN pg_namespace AS n ON n.oid=t.typnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND o.contype='c'

UNION SELECT 
oid,
'' AS container_name,
'DATABASE' AS container_type,
evtname AS object_name,
'EVENT TRIGGER' AS object_type
FROM pg_event_trigger 

),
named_user_defined_objects AS (
SELECT oid, container_name, container_type, object_name, 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)
)
SELECT container_name, container_type, object_name, 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
ORDER BY container_type, container_name, object_type, 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 .

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