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

Seq nr	Name	Goal	Type	Data source▲	Last update	License	...
1	Address field size is incorrect (too short or too long)	Find base table columns that are meant for recording different types of addresses where the filed size does not take into account the possible maximum length.	Problem detection	INFORMATION_SCHEMA only	2023-11-09 12:55	MIT License	View
2	All CHECK constraints of domains that are not associated with any table	Find all CHECK constraints (except NOT NULL) of domains that are not associated with any column.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
3	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
4	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
5	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
6	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
7	All domain default values	Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values.	General	INFORMATION_SCHEMA only	2021-01-19 13:04	MIT License	View
8	All domains	Find all domains.	General	INFORMATION_SCHEMA only	2020-11-10 11:49	MIT License	View
9	All foreign tables	Find all foreign tables.	General	INFORMATION_SCHEMA only	2020-11-06 15:20	MIT License	View
10	All table CHECK constraints that cover at leat one column	Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications).	General	INFORMATION_SCHEMA only	2024-01-01 11:27	MIT License	View
11	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
12	All updatable views	Find all views through which it is possible to modify data in base tables. Is_insertable_into and is_updatable show as to whether the view is naturally updatable in PostgreSQL or has associated rules that make sure that INSERT/UPDATE/DELETE operations against the view will not cause an error. Please note that the rule could be DO INSTEAD NOTHING rule, i.e., data modification through the view does not cause an error but the data is not actually modified. If the updatability is achieved due to rules, then is_updatable=YES if the view has both UPDATE and DELETE rule that make sure that UPDATE and DELETE operations do not cause an error.	General	INFORMATION_SCHEMA only	2024-01-14 17:09	MIT License	View
13	All user schemas	Find all the schemas in the database that are not used for the system purposes.	General	INFORMATION_SCHEMA only	2022-11-04 15:14	MIT License	View
14	Base table column name is the same as its domain name	Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
15	Base table column of comments/descriptions has an incorrect data type or maximum character length	Find base table columns that name refers to the possibility that these are used to register comments/descriptions. Find the columns where the data type is not VARCHAR and TEXT or in case of VARCHAR the maximum number of permitted characters is smaller than 1000. In case of determining field sizes choose a size that permits registration of all possible legal values.	Problem detection	INFORMATION_SCHEMA only	2022-05-01 13:39	MIT License	View
16	Base table column of measurements does not have a correct data type	Find base table columns that name refers to the possibility that these are used to register measurement results. Find the columns that do not have an integer or numeric type.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
17	Base table column of national identification numbers does not have a correct data type	Find non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
18	Base table column of national identification numbers has a too short field size	Find base table columns with VARCHAR type that name refers to the possibility that these are used to register national identification numbers (personal codes). Find the columns where the field size is shorter than 20.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
19	Base table column of personal names has questionable properties	Find base table columns that name refers to the possibility that these are used to register personal names. Although there are very long personal names the general approach is to register a shortened version of these. Thus, a large field size is not a good idea because it would cause usability and security problems. There are persons who only have one name (mononymous persons). Database design must take it into account.	Problem detection	INFORMATION_SCHEMA only	2022-10-29 20:35	MIT License	View
20	Base table column of quantities does not have a numeric type (based on column names)	Find base table columns that name refers to the possibility that these are used to register quantities of things. Find the columns that do not have a numeric type.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View