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	...
81	Do not use FLOAT Data Type	Find base table columns that have FLOAT, REAL, or DOUBLE PRECISION type. "The data types real and double precision are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it." (PostgreSQL documentation) Do not use the approximate numeric types FLOAT, REAL, and DOUBLE PRECISION in order to present fractional numeric data. Due to the use of the IEEE 754 standard the results of calculations with the values, which have one of these types, can be inexact because out of necessity some numbers must be rounded to a value, which is very close. "Comparing two floating-point values for equality might not always work as expected." (PostgreSQL documentation)	Problem detection	INFORMATION_SCHEMA only	2021-03-12 15:41	MIT License	View
82	Do not use the money data type	Find base table columns with the Money data type. Each value of the money type has associated currency sign that depends on server settings. It could be $. Moreover, using the values for arithmetic operations requires casts that makes the code more complicated.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
83	Do you really need fractional seconds?	Find default values that return current timestamp with the maximum number of fractional seconds (6).	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
84	Duplicate CHECK constraints that are connected directly to a table	The same table should not have multiple CHECK constraints with exactly the same Boolean expression. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
85	Duplicate CHECK constraints that are connected to a domain	The same domain should not have multiple CHECK constraints with exactly the same Boolean expression. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
86	Duplicate DEFAULT values of base table columns	Find base table columns that have both default value determined through a domain and default value that is directly attached to the column. Do not duplicate specifications of default values to avoid confusion and surprises. If column and domain both have a default value, then in case of inserting data the default value that is associated directly with the column is used.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
87	Duplicate stored generated base table columns	Find base tables that have more than one stored generated column with the same expression. The support of generated columns was added to PostgreSQL 12. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
88	Duplicate views	Find views with exactly the same subquery. There should not be multiple views with exactly the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
89	Field size is not sufficiently different in case of base table columns	Find base table columns that name refers to the possibility that these are used to register names. Find base table columns that name refers to the possibility that these are used to register comments/descriptions/etc. Find the cases where a base table contains columns from the both sets and the field size in case of the latter is not at least twice as big as in case of the former. For example, if the name of a service can be 100 character long, then it is quite illogical that the description of the service is only at most 200 characters long.	Problem detection	INFORMATION_SCHEMA only	2021-03-27 18:51	MIT License	View
90	Generated stored base table columns	Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
91	Generated stored base table columns duplicates another column in the table	Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
92	Generated stored base table columns that expression does not refer to any column	Find generated stored base table columns that expression does not refer to any column of the table. It could be that there will be a constant value in every row in case of this column. The support of generated columns was added to PostgreSQL 12.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
93	Grantees	Database must be used by users who have minimal set of privileges for performing tasks. The query helps to find out as to whether some user/role other than PUBLIC and a superuser have rights to use tables and routines of the database.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
94	Inappropriate field size or data type for column that strores database username	Find columns of base tables that based on the default value of the column contain database username. However, the type of the column is not VARCHAR(63) or VARCHAR(128).	Problem detection	INFORMATION_SCHEMA only	2023-11-19 11:58	MIT License	View
95	Inconsistency between the name and the type of a base table column (dates)	Find base table columns that name refers to the possibility that these are used to register dates. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column data type is "date", then the suffix of the column name should be "kp" (Estonian) or "date" (English).	Problem detection	INFORMATION_SCHEMA only	2021-03-27 16:16	MIT License	View
96	Inconsistency between the name and the type of a base table column (timestamps)	Find base table columns that name refers to the possibility that these are used to register timestamps. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column type is "timestamp", then the suffix of the column name should be "aeg" (Estonian) or "time" (English).	Problem detection	INFORMATION_SCHEMA only	2021-03-28 15:12	MIT License	View
97	Inconsistency between the type and the default value of a column (date and timestamp values)	Find table columns with timestamp/date types that data type and dynamically found default value have a different type.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
98	Inconsistency between the type and the default value of a column (time values)	Find table columns with time types, which data type and dynamically found default value have a different type.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
99	Inconsistent data type usage in case of registering a date	Find as to whether there are multiple different types used in case of columns that are meant for registering dates.	Problem detection	INFORMATION_SCHEMA only	2021-03-21 18:32	MIT License	View
100	Inconsistent data type usage in case of registering a symbol	Find whether the database uses both CHAR(1) and VARCHAR(1) columns to register a single symbol.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View