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 registration of classifiers.
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 7 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	A state machine is implemented with the help of a state classifier table	Find implementations of state machines that use a state classifier table.	General	system catalog base tables only	2023-11-26 15:39	MIT License	View
2	Do not leave out the referential constraints (based on classifiers)	Find non-key and non-foreign columns of base tables with a textual column and small field size in case of which there is a table with the name that is similar to the column name. Perhaps the table is a classifier table and the column should have a foreign key constraint referencing to the table.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-18 11:14	MIT License	View
3	Multiple tables share the same state classifier	Find cases where multiple tables share the same state classifier. For each main entity type one should create a separate state classifier table. Even if the classifier values are the same in case of two entity types for now these may become different in the future. Having a shared state classifier table usually means very simplistic state machines (states active and inactive) that could point to the gaps in analysis.	Problem detection	system catalog base tables only	2023-12-30 15:51	MIT License	View
4	One true lookup table	Find tables that contain all (or most) of the classifier values and tables that refer to these.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
5	Potentially a classifier is missing (based on field sizes)	Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less.	General	INFORMATION_SCHEMA+system catalog base tables	2021-03-10 13:07	MIT License	View
6	Potentially a classifier table is missing (based on field sizes)	Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less and where the column name does not refer to names or comments. Return only data about these columns where there is no table with a similar name. Return data only about tables that could be referenced from more than one table.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-18 20:08	MIT License	View
7	Sequence generators not needed	Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-18 10:00	MIT License	View