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	...
161	Registration/modification time is not mandatory	Find columns that contain registration or modification time but are optional.	Problem detection	INFORMATION_SCHEMA only	2023-11-26 16:51	MIT License	View
162	Should the time zone be recorded in case of time or not?	Find all the base table columns that have the type time without time zone or time with time zone. Return the data only if there is at least one column with the type time without time zone and one column with the type time with time zone.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
163	Should the time zone be recorded in case of timestamp or not?	Find all the base table columns that have the type timestamp without time zone or timestamp with time zone. Return the data only if there is at least one column with the type timestamp without time zone and one column with the type timestamp with time zone.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
164	Stating the obvious (column names)	Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer.	Problem detection	INFORMATION_SCHEMA only	2023-11-04 15:19	MIT License	View
165	Storing file content in the database	Find columns that probably store content of files in the database.	General	INFORMATION_SCHEMA only	2021-03-27 17:02	MIT License	View
166	System-generated domain CHECK constraint names	Find the names of domain CHECK constraints that have been system-generated. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.	Problem detection	INFORMATION_SCHEMA only	2022-10-27 15:44	MIT License	View
167	Table check constraints with regular expressions	Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column and use a regular expression. 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	2022-12-13 12:47	MIT License	View
168	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
169	Table columns with NOT VALID CHECK constraints	Find CHECK constraints of base table and foreign table columns that are not valid. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
170	Table privileges	Check as to whether there are no unnecessary privileges.	General	INFORMATION_SCHEMA only	2020-12-29 10:38	MIT License	View
171	Table privileges have been granted to PUBLIC	You should follow the principle of least privilege and thus not have in your database tables that usage privileges are granted to the pseudo-role PUBLIC, i.e., to all the database users now and in the future.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
172	The expression of a check constraint that is associated with a domain needs type conversion	Find check constraints of domains where the Boolean expression invokes an operation that does not match with the data type of the domain.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
173	The maximum number of characters may be missing	Perhaps the character maximum length has been omitted accidentally, i.e., one wrote VARCHAR instead of VARCHAR(n) where n is the maximum permitted number of characters in the field value. VARCHAR and TEXT are synonyms.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
174	The number and percentage of base tables where all the non-primary key columns are optional	Find the number and percentage of base tables where all the non-primary key columns are optional	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 19:57	MIT License	View
175	The number and percentage of base tables without CHECK constraints	Find the extent in which data integrity is checked at the database level. Find the number and percentage of base tables that do not have any associated CHECK constraints.	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 19:15	MIT License	View
176	The number and percentage of base tables without keys	Find the extent in which repeating rows are permitted in the database. Find the number and percentage (from the total number of base tables) of base tables that do not have the PRIMARY KEY constraint and also do not have any UNIQUE constraints.	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 19:16	MIT License	View
177	The number and percentage of optinal columns in base tables	Find the number and percentage of optinal columns in base tables	Sofware measure	INFORMATION_SCHEMA only	2020-11-08 21:17	MIT License	View
178	The number of base table columns based on data type	Find the number of base table columns based on data type.	Sofware measure	INFORMATION_SCHEMA only	2020-11-19 15:30	MIT License	View
179	The number of base table columns by type category	Find the number of base table columns based on the category of data type that the columns have.	Sofware measure	INFORMATION_SCHEMA only	2021-03-20 12:27	MIT License	View
180	The number of default values	Find the total number of columns with a default value as well as the number of columns with different kinds of default values (the number of columns where the default is used to implement surrogate key, the number of columns where the default is not used to implement surrogate key, the number of columns with a static default value, the number of columns with a dynamic default value).	Sofware measure	INFORMATION_SCHEMA only	2021-11-22 13:26	MIT License	View