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	...
21	Perhaps too many square brackets	Character classes are surrounded by two pairs of square brackets.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-15 10:47	MIT License	View
22	Check as to wheteher the names of parameters are in the plural or in the singular form (English version)	Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:08	MIT License	View
23	Check as to wheteher the names of parameters are in the plural or in the singular form (Estonian version)	Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-11-15 16:53	MIT License	View
24	Check as to wheteher the names of columns are in the plural or in the singular form (English version)	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.	General	INFORMATION_SCHEMA+system catalog base tables	2023-03-20 13:18	MIT License	View
25	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
26	Check as to wheteher the names of columns are in the plural or in the singular form (Estonian version)	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.	General	INFORMATION_SCHEMA+system catalog base tables	2023-10-26 17:01	MIT License	View
27	Check as to wheteher the names of tables are in the plural or in the singular form (English version)	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.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:09	MIT License	View
28	Check as to wheteher the names of tables are in the plural or in the singular form (Estonian version)	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.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:09	MIT License	View
29	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
30	Table privileges	Check as to whether there are no unnecessary privileges.	General	INFORMATION_SCHEMA only	2020-12-29 10:38	MIT License	View
31	CHECK constraints with the cardinality bigger than one that involve the same set of columns	CHECK constraints with the cardinality bigger than one that involve the same set of columns. Make sure that there is no duplication.	General	system catalog base tables only	2023-12-25 12:39	MIT License	View
32	CHAR columns have a default value that length is shorter from the character maximum length of the column	Choose a suitable data type, field size, and default value. If the default value is shorter from the character maximum length, then spaces will be added to the end of the registered value.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
33	Multiple deletes from the same table in a routine	Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of deleting different rows of the same table with different DELETE statements one should try to do it with one statement. The query excludes routines where IF statement is used.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-24 14:15	MIT License	View
34	Multiple selects from the same table in a routine	Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:10	MIT License	View
35	Multiple updates of the same table in a routine	Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of updating different fields of the same table with different UPDATE statements one should try to do it with one statement.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-24 13:59	MIT License	View
36	Function in a function-based index of a column is different of the function that is used in the query in a routine based on the column	Create appropriate indexes to speed up queries. If you apply a function to a column in a query, then create a function-based index based on the function to the column.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 17:26	MIT License	View
37	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
38	Mixing the use of TEXT and VARCHAR type in case of base table columns	Declaring a column to have the type TEXT or the type VARCHAR (without the maximum number of characters) has the same end result in terms of what data can be recorded in the column. Nevertheless, one should try to stick with using one of the type names in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)	Problem detection	INFORMATION_SCHEMA only	2021-03-12 15:06	MIT License	View
39	Delimited identifiers	Delimited identifiers (quoted identifiers) are case sensitive. Identifiers of database objects should be case insensitive in order to simplify their management.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-03-18 16:38	MIT License	View
40	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