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 the means that have been used to make the use or management of database more comfortable and thus, more efficient.
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 101 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	All column DEFAULT values	Find all the default values of base table, view, and foreign table columns.	General	INFORMATION_SCHEMA only	2022-10-31 10:18	MIT License	View
2	All column dynamic DEFAULT values values that do not invoke a sequence	Find all columns that have a dynamic default value, i.e., the value is returned by a function but the function is not for invoking a sequence.	General	INFORMATION_SCHEMA only	2021-11-15 15:57	MIT License	View
3	All columns of a base table have a default value	Find base tables where all the columns have a default value.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
4	All column static DEFAULT values	Find all columns that have a static default value, i.e., the value is not returned by a function.	General	INFORMATION_SCHEMA only	2021-11-15 15:57	MIT License	View
5	All declaratively partitioned tables	Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 15:13	MIT License	View
6	All the non-primary key columns are optional	Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
7	Base table columns with the same name and type have different field sizes	Find base table columns that have the same name and type but different field size.	Problem detection	INFORMATION_SCHEMA only	2021-03-28 16:59	MIT License	View
8	Base table columns with the same name have different types	Find base table columns that have the same name but different type. In general, base tables columns that have the same name should have the same type as well.	Problem detection	INFORMATION_SCHEMA only	2023-01-14 20:54	MIT License	View
9	Candidate key columns that have a static default value	Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-12 11:32	MIT License	View
10	Candidate keys where all columns have a static default value	Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-12 11:33	MIT License	View
11	Cascading update is not needed (based on surrogate keys)	Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-28 12:31	MIT License	View
12	Chains of ON DELETE CASCADE	Find all referential paths (chains of of parent-child tables that are associated through foreign key constraints) where all foreign key constraints have ON DELETE CASCADE compensating actions. Be careful with too long chains.	General	system catalog base tables only	2022-10-29 20:04	MIT License	View
13	Column names that make joining more difficult (foreign key column name contains the table name)	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different but the difference comes from the fact that the foreign key column name starts or ends with the table name. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-12 10:26	MIT License	View
14	Column names that make joining tables more difficult	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-08 13:56	MIT License	View
15	Column names that make joining tables more difficult (quite similar names)	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is between two and four characters. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-08 13:56	MIT License	View
16	Column names that make joining tables more difficult (very similar names)	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is one character. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-08 13:56	MIT License	View
17	Columns of base tables that hold truth values but do not have a default value (Boolean columns)	Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type.	Problem detection	INFORMATION_SCHEMA only	2023-11-09 13:14	MIT License	View
18	Columns of base tables that hold truth values but do not have a default value (non-Boolean columns)	Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-20 14:08	MIT License	View
19	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
20	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