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	...
741	Number of rows in base tables	Find the number of rows in base tables.	General	system catalog base tables only	2022-10-27 19:44	MIT License	View
742	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
743	The number of user-defined rules	Find the number of rules in a database, excluding the rules that are associated with system catalog tables and rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives some indications about the state of enforcing constraints at the database level.	Sofware measure	system catalog base tables only	2022-10-21 11:19	MIT License	View
744	The number of user-defined rules by schema and in total	Find the number of rules in a database in different schemas, excluding the rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives an indication about the state of enforcing constraints at the database level.	Sofware measure	system catalog base tables only	2022-10-21 11:29	MIT License	View
745	Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column)	Find the number of system-generated constraint names by constraint type. 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.	Sofware measure	system catalog base tables only	2023-01-10 14:53	MIT License	View
746	Number of system-generated and user-defined constraint names by constraint type (constraints that involve one column)	Find the number of system-generated constraint names by constraint type. 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.	Sofware measure	system catalog base tables only	2023-01-10 14:52	MIT License	View
747	The number of table constraints with the cardinality bigger than one	Find the number of table constraints with the cardinality bigger than one. Find the number, based on the cardinality, constraint type, and cardinality+constraint type.	Sofware measure	system catalog base tables only	2020-11-19 13:29	MIT License	View
748	The number of tables by schema, by type, and in total	Find the number of tables (base, foreign, and derived) in different schemas.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2021-03-07 10:57	MIT License	View
749	The proportion of mandatory and optional textual base table columns	Find the number of textual base table columns, the number of optional textual base table columns (permit NULLs), and the number of mandatory textual base table columns (do not permit NULLs).	Sofware measure	INFORMATION_SCHEMA only	2021-01-15 17:39	MIT License	View
750	The generic names (columns) (aggregate view)(2)	Find the number of too generic base table and view column names. Do not differentiate between base table and view columns to determine what name is generic.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-01-18 16:42	MIT License	View
751	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
752	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
753	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
754	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
755	The use of xmin hidden column in views and routines	Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 11:56	MIT License	View
756	Number of views with and without security barrier	Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2024-12-13 19:41	MIT License	View
757	Very similar column names	Find the pairs of table columns that name is different only by one symbol and that have the same type and/or domain.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-03-18 20:25	MIT License	View
758	Patterns of the names of columns of simple primary keys	Find the patterns of the names of columns of simple primary keys. Make sure that the naming is consistent. Ideally, the names should indicate as to whether the column is a surrogate or a natural key column. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog.	General	system catalog base tables only	2023-01-20 13:40	MIT License	View
759	Patterns of the names of triggers and rules	Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:51	MIT License	View
760	All database privileges	Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY.	General	system catalog base tables only	2021-11-23 20:28	MIT License	View