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 996 queries.

Seq nr	Name	Goal▲	Type	Data source	Last update	License	...
921	Table columns that are associated with a sequence generator	Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there is no usage of sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys.	General	INFORMATION_SCHEMA only	2021-03-07 21:06	MIT License	View
922	Base table columns with the type VARCHAR(1)	The choice of data types should reveal as much as possible about the nature of the data in the column. The type of these columns could be CHAR(1) and they should have a constraint that a value in the column cannot be an empty string.	Problem detection	INFORMATION_SCHEMA only	2024-12-14 13:41	MIT License	View
923	Columns with exact/floating numeric types have textual default values	The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
924	Too short table constraint names	The names should be expressive. Find names of constraints, which are associated directly to a table, that are shorter than the length of the name of the table + two characters.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
925	Names of triggers and rules that do contain the table name	The names should contain table name in order to make the names better understandable.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-13 19:23	MIT License	View
926	Names of triggers and rules that do not contain the table name	The names should contain table name in order to make the names better understandable.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-13 19:18	MIT License	View
927	Different prefixes of a candidate key column and a referencing foreign key column	The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different prefixes. Thus, for instance, one cannot use USING syntax for joining the tables.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
928	Different suffixes of a candidate key column and a referencing foreign key column	The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different suffixes. Thus, for instance, one cannot use USING syntax for joining the tables.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
929	Names of columns with the type BOOLEAN	The naming of BOOLEAN columns must be consistent. For the better readability the names of such columns could have prefix "is_" (in English) or "on_" (in Estonian)	General	INFORMATION_SCHEMA+system catalog base tables	2024-01-03 09:41	MIT License	View
930	Columns with BOOLEAN type that do not have a good name	The phrase "is_" or "has_" or "can_" (in English) or "on_" (in Estonian) should be used in the name - preferably in the beginning. Worse: agreed, kinnitatud. Better: contract_is_agreed, leping_on_kinnitatud. Perhaps the best: is_contract_agreed or is_agreement, on_leping_kinnitatud, on_kinnitatud.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-03 09:56	MIT License	View
931	Cannot accommodate all the fractional seconds in case of table columns	The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
932	Columns with BOOLEAN type that do have a good name	The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: agreed, kinnitatud. Better: is_agreement, on_kinnitatud.	General	INFORMATION_SCHEMA+system catalog base tables	2024-01-03 09:56	MIT License	View
933	Routines with BOOLEAN return type that do not have a good name	The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: check_rights. Better: has_rights.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-27 11:12	MIT License	View
934	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
935	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
936	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
937	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
938	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
939	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
940	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