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:
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 961 queries.

Seq nr	Name	Goal▲	Type	Data source	Last update	License	...
901	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
902	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
903	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
904	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
905	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
906	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
907	Cycle in a hierarchy	There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered.	Problem detection	system catalog base tables only	2021-02-25 17:30	MIT License	View
908	PL/pgSQL functions without the RETURN clause	The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returning void, however. In those cases a RETURN statement is automatically executed if the top-level block finishes. It also does not apply to trigger functions that only task is to raise an exception.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-10-29 11:13	MIT License	View
909	Duplicate CHECK constraints that are connected to a domain	The same domain should not have multiple CHECK constraints with exactly the same Boolean expression. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
910	Duplicate CHECK constraints that are connected directly to a table	The same table should not have multiple CHECK constraints with exactly the same Boolean expression. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
911	Logical size of a schema	The schema size is the sum of the sizes of all tables in the schema.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2022-10-21 16:18	MIT License	View
912	Disabled system triggers (i.e., disabled enforcement of constraints)	These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work.	Problem detection	system catalog base tables only	2021-03-12 15:06	MIT License	View
913	Logical size of a table	The table size is the sum of the total size of the simple columns and the total size of the complex columns in the table. In case of SQL databases large base tables in terms of number of columns could be a side effect of the problems with cloned columns or multiple columns for the same attribute. A base table with a low normalization level, which is meant to hold data that corresponds to multiple entity types has typically also relatively large number of columns compared with other base tables. Thus, the normalization level of base tables with a large number of columns should be checked as well.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2022-10-21 16:17	MIT License	View
914	Pointlessly returned value	The value that is returned by a function should depend on something. If you do not have anything meaningful to return (for instance, the function always returns true, false, NULL, or the same number), then write VOID instead of the return type.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 15:01	MIT License	View
915	Referential degree of tables	This metric represents the number of foreign keys in a base table.	Sofware measure	INFORMATION_SCHEMA only	2021-03-12 11:07	MIT License	View
916	Perhaps too many input parameters	Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 10:57	MIT License	View
917	FILLFACTOR is probably too small	Too small fillfactor wastes storage space.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-11-02 10:43	MIT License	View
918	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
919	The number of user-defined triggers by schema, by type, and in total	Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
920	The number of user-defined triggers	Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View