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:	Numeric values (software measures) about the database
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 86 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	All supertables	Find all the base tables that serve as supertables in the inheritance hierarchies	Sofware measure	system catalog base tables only	2021-03-14 17:07	MIT License	View
2	Check as to wheteher the names of columns are in the plural or in the singular form (English version) (aggregate view)	Check as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming. Show the number of columns that name is in plural or in singular by table type.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-03-20 13:19	MIT License	View
3	Check as to wheteher the names of tables are in the plural or in the singular form (English version) (aggregate view)	Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming. Show the number of tables that name is in plural or in singular by table type.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:09	MIT License	View
4	Consistency of using NOT NULL constraints on Boolean base table columns	Find the number of mandatory and optional Boolean base table columns and the proportion of optional columns from all the Boolean columns. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory.	Sofware measure	INFORMATION_SCHEMA only	2020-12-28 01:43	MIT License	View
5	Depth of referential tree of a schema	Depth of referential tree of a database schema is the longest referential path between the tables in this schema (Piattini et al., 2001). In other words, it is the biggest value among the DRT(T) values of all the tables of the schema.	Sofware measure	system catalog base tables only	2020-11-14 15:28	MIT License	View
6	Depth of relational tree of a table	Depth of relational tree of a table T (DRT(T)) is defined by Piattini et al. (2001) as "the longest referential path between tables, from the table T to any other table in the schema". The result may help to classify the data. If the depth is 0, then probably the table contains classifers. Tables with the largest depth probably contain some extra information about main entities.	Sofware measure	system catalog base tables only	2020-11-14 16:13	MIT License	View
7	Different character maximum lengths that are used to define textual base table columns	Find the number of different character maximum lengths that are used to define textual base table columns as well as list all the different lengths. Show also the total number of columns with char/varchar type. Maximum character length constrains values in a column. Thus, in case there is a small number of used lengths, it raises a question as to whether the lengths have been optimally selected.	Sofware measure	INFORMATION_SCHEMA only	2021-03-26 11:24	MIT License	View
8	Different data types that are used to define base table columns	Find the number of different data types that are used to define base table columns as well as list all the different types. Data type constrains values in a column. Thus, in case there is a small number of used types, it raises a question as to whether the types have been optimally selected.	Sofware measure	INFORMATION_SCHEMA only	2020-11-27 10:40	MIT License	View
9	Frequency of column name lengths based on the table type	Find in case of base tables, materialized views, and views the number of columns based on the length of the column name.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2022-11-22 14:43	MIT License	View
10	Frequency of lengths of the names of database objects	"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) Names should be expressive. Find how many names (identifiers) of database objects there are with different lengths. The values could be used to compare different databases.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:39	MIT License	View
11	Frequency of name components of database objects	"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) Names should be expressive. Find, what are the most popular components of the names (identifiers) of user-defined database objects, assuming, that the separator of the components is "_".	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:50	MIT License	View
12	Frequency of table name lengths based on the table type	Find in case of base tables, materialized views, and views the number of tables based on the length of the table name.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2022-11-23 01:09	MIT License	View
13	Frequent names of database objects	"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) Names should be expressive. Find the names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average. Also make sure that there is no duplication in play.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:49	MIT License	View
14	Frequent names of database objects by object type	"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) Names should be expressive. Find the names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average in case of the particular type of database objects. Also make sure that there is no duplication in play.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:24	MIT License	View
15	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
16	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
17	Median and average number of subcomponents in the names of database objects	"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) Names should be expressive. Find the median and average (arithmetic mean) number of subcomponents in the names of user-defined database objects. The values could be used to compare different databases.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:47	MIT License	View
18	Median and average of the length of names of database objects	"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) Names should be expressive. Find the median length and average (arithmetic mean) length of the names (identifiers) of user-defined database objects. The values could be used to compare different databases.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:25	MIT License	View
19	Median and average of the length of names of database objects by the object type	"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) Names should be expressive. Find the median length and average (arithmetic mean) length of the names (identifiers) of user-defined database objects by the object type. The values could be used to compare different databases.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:37	MIT License	View
20	Name does not contain any vowels (aggregate view)	Find aggregate information about the names of database objects that do not contain any vowels.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:19	MIT License	View