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

Seq nr	Name	Goal	Type	Data source	Last update▼	License	...
161	Inconsistent use of older and newer join syntax in the subqueries of derived tables	Find as to whether the subqueries of derived tables use both older join syntax (join condition in the where clause) and newer syntax (join condition in the from clause).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-24 13:26	MIT License	View
162	Unused indexes	Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-19 00:23	MIT License	View
163	The number of routines by the number of input parameters	Find the number of user-defined non-trigger routines by the number of input parameters.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-16 09:43	MIT License	View
164	The number of user-defined routines based on their database usage	Find the number of non-extension routines in a database that modify data. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. The query distinguishes trigger and non-trigger routines, thus it also gives information about triggers that modify data.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-15 00:38	MIT License	View
165	The number of user-defined non-trigger routines by schema, by language, and by routine type	Find the number of user-defined non-trigger routines based on their schema, language, and routine type. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 20:53	MIT License	View
166	The number of user-defined non-trigger routines by schema, by security type, and by being deterministic	Find the number of user-defined non-trigger routines based on their schema, security type, and being deterministic. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 20:52	MIT License	View
167	The number of commented routines	Find the number of commented routines based on the way how the comments have been added (as a COMMENT object or as a comment in the routine body).	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 20:49	MIT License	View
168	The number of user defined triggers by different characteristics	Find the number of user defined triggers by action orientation (ROW, STATEMENT), action timing (BEFORE, AFTER, INSTEAD OF), and event type (INSERT, UPDATE, DELETE) and their combinations.	Sofware measure	INFORMATION_SCHEMA only	2024-01-14 17:41	MIT License	View
169	Updatable views with WHERE clause that do not have WITH CHECK OPTION constraint	Find updatable views that restrict rows, i.e., have WHERE clause, but do not have WITH CHECK OPTION constraint. WITH CHECK OPTION constraint prevents updates through the view that violate the predicate of the view. Such updates must be prevented.	Problem detection	INFORMATION_SCHEMA only	2024-01-14 17:11	MIT License	View
170	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
171	Different tasks of rules	Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task.	General	system catalog base tables only	2024-01-14 16:10	MIT License	View
172	The number of rules by different characteristics	Find the number of rules by event (SELECT, INSERT, UPDATE, and DELETE) and in total.	Sofware measure	system catalog base tables only	2024-01-14 16:10	MIT License	View
173	Number of used tables	Find statistics about how many derived tables have how many different underlying tables.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 13:43	MIT License	View
174	Number of using views	Find statistics about how many base tables have how many derived tables that use these tables.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 13:42	MIT License	View
175	Number of derived tables that aggregate data	Find the number of derived tables that aggregate data.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 12:33	MIT License	View
176	Usage of base tables	Find for each derived table the list of base tables that are used by the derived table.	General	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 00:50	MIT License	View
177	Grantable column privileges	Find column privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
178	Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers	Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	system catalog base tables only	2024-01-07 13:43	MIT License	View
179	Grantable routine privileges	Find routine privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
180	Grantable table privileges	Find table privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View