PostgreSQL Design Check Queries

Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

	Choose collection:
AND	Choose query type:
AND	Choose query reliability:
AND	Choose category:	Queries of this category provide information about comments to the database objects that have been registered in the system catalog.
AND	Choose data source:	From where does the query gets its information?
AND	Enter string:
AND	Has fixing queries?

All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
Statistics about the catalog content and project home in GitHub that has additional information.

There are 11 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	Comments of columns	Find all comments of columns of tables.	General	system catalog base tables only	2023-10-18 14:09	MIT License	View
2	Comments of derived tables	Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information.	General	system catalog base tables only	2023-01-19 12:14	MIT License	View
3	Comments of non-derived tables	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.	General	system catalog base tables only	2023-01-19 12:30	MIT License	View
4	Comments of routines	Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
5	Comments of schemas, sequences, types, domains, domain constraints, and event triggers	Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers.	General	system catalog base tables only	2023-01-19 15:04	MIT License	View
6	Consistency of comments of routines	Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 10:56	MIT License	View
7	Duplicate comments	Find comments that have been registered with a COMMENT statement and that are associated with more than one object. It would probably mean that a comment is incorrect or missing.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-03-16 20:33	MIT License	View
8	The number of commented routines	Find the number of commented routines based on the way how the comments have been added (as a COMMENT object or as a comment in the routine body).	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 20:49	MIT License	View
9	The reference to a database operation is missing from a comment	Find user-defined routines that comment does not contain a reference to a database operation that the routine implements. In case of routines that have been created based on the contracts of database operations, one should refer to the short identifier of the operation in the comment of the routine. The operation identifier must be in this case in the form OP, but other forms could be used as well.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 11:39	MIT License	View
10	Too short or missing comments of derived tables and routines	Find views, materialized views, and user-defined routines that do not have a comment at all or the comment is shorter than twice the length of the object name, or the comment states that it is missing (TODO). You should give information to future developers and maintainers of the system (including the future version of yourself). Do not just repeat the name in the comment (with perhaps some rewording).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-19 12:56	MIT License	View
11	Wrong comment - trigger function does not implement a database operation	Trigger functions should not contain references to database operations. Perhaps the trigger implements ensuring some invariant of the operation but it does not implement the operation itself.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View