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 states of entities in a database.
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 10 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	A state machine is implemented with the help of an enumeration type	Find implementations of state machines that uses an enumeration type.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-26 15:39	MIT License	View
2	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
3	Columns of base tables that hold truth values that do not have a default value although they could have it (Boolean columns)	Find columns of base tables that have type BOOLEAN. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column.	Problem detection	INFORMATION_SCHEMA only	2023-11-09 16:39	MIT License	View
4	Columns of base tables that hold truth values that do not have a default value although they could have it (non-Boolean columns)	Find columns of base tables that do not have type BOOLEAN but are used to record Boolean values. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 16:40	MIT License	View
5	Inconsistent names of database objects that are used to manage the state of main objects in the database	"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term. For instance, it is a bad practice to use word "state" in table names but word "status" in function names.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:24	MIT License	View
6	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
7	Names of database objects that are used to manage the state of main objects in the database	"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term.	General	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:23	MIT License	View
8	Perhaps a state machine is implemented with Boolean columns	Find implementations of state machines that uses a set of one or more Boolean columns. These columns could have the type Boolean or could probably (based on the column name and non-participation in a foreign key) contain values that represent truth values.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 13:22	MIT License	View
9	Perhaps a state machine is implemented with timestamp columns	Find implementations of state machines that uses a set of columns with a timestamp type.	Problem detection	INFORMATION_SCHEMA only	2021-03-26 21:04	MIT License	View
10	Perhaps a too simplified state machine	Find base table columns with Boolean type that name refers to the possibility that these are used to register as to whether an entity is currently in active state or not. Find the base tables that have exactly one Boolean column. During the system design one should find all the possible states of an entity type that influence the behavior of the information system. Data as to whether an entity is in one of these states should be in the database. Having only two states - active/inactive - is sometimes a too big simplification.	Problem detection	INFORMATION_SCHEMA only	2021-03-27 03:08	MIT License	View