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 could be used specifically in the learning environment to assess as to whether student projects have filled certain criteria.
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	A large number of triggers	Show user-defined triggers if there are more than 9 different trigger routine bodies, i.e., different triggers on different tables that do the same thing count as one trigger.	General	INFORMATION_SCHEMA+system catalog base tables	2023-12-25 11:23	MIT License	View
2	Are there enough routines that implement database operations?	Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-03 20:07	MIT License	View
3	Base tables with plenty of data	Find base tables that have 1000 rows or more.	General	system catalog base tables only	2022-10-21 11:25	MIT License	View
4	Exclude constraint to prevent overlapping time periods	Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods.	General	INFORMATION_SCHEMA+system catalog base tables	2023-11-07 00:44	MIT License	View
5	Insufficient number of user-defined base tables	There must be at least n (seven in this case) user-defined base tables in the database.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
6	Insufficient number of user-defined domains	There must be at least n (one in this case) user-defined domains in the database each of that must be used in case of at least two columns of base tables.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
7	Insufficient number of user-defined foreign tables	There must be at least n (two in this case) user-defined foreign tables in the database.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
8	Insufficient number of user-defined non-trigger routines	There must be at least n (four in this case) user-defined non-trigger routines in the database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
9	Insufficient number of user-defined triggers+rules	There must be user-defined triggers and/or rules for at least n (three in this case) tasks in the database. It also means that one should create at least three triggers and/or rules in the database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-04 11:47	MIT License	View
10	Insufficient number of user-defined triggers+rules (based on number of tasks)	There must be user-defined triggers and/or rules for at least n (three in this case) tasks in the database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-04 11:46	MIT License	View
11	Insufficient number of user-defined views	There must be at least n (four in this case) user-defined views in the database.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View