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	...
921	Useless type indication	Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-10 14:47	MIT License	View
922	Useless type indication (2)	Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-10 14:55	MIT License	View
923	User-defined composite types	Find composite types that are created by a user, i.e., the type is not created automatically by the database management system based on a relation.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
924	User-defined derived tables	Find user-defined views and materialized views. Pay attention to the outer join operations. One should use these if and only if there is a real need for them. Otherwise they just reduce performance. On the other hand, pay attention that outer join is used where it is logically needed.	General	INFORMATION_SCHEMA+system catalog base tables	2023-12-11 15:23	MIT License	View
925	User-defined non-trigger routines without parameters	Find user-defined non-trigger routines with no parameters.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-03 20:05	MIT License	View
926	User-defined non-trigger SQL and PL/pgSQL routines	Find the non-trigger SQL and PL/pgSQL routines in non-systemic schemas.	General	INFORMATION_SCHEMA+system catalog base tables	2021-12-23 02:40	MIT License	View
927	User-defined routine execution privilege has been granted to PUBLIC	You should follow the principle of least privilege and thus not have in your database user-defined routines that execution privilege is granted to PUBLIC, i.e., to all the database users now and in the future. By default, PostgreSQL gives routine execution privileges to PUBLIC.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
928	User-defined routines that have the same name as some system-defined routine.	Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
929	User-defined routines that implement UPSERT operation	Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations.	General	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 17:02	MIT License	View
930	User-defined routines that produce a temporary table	Find user user-defined routines that produce a temporary table	General	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 16:25	MIT License	View
931	User-defined routines that read data	Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query.	General	INFORMATION_SCHEMA+system catalog base tables	2023-12-21 15:00	MIT License	View
932	User-defined routines that use dynamic SQL to execute data manipulation statements	Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:39	MIT License	View
933	User-defined routines that use keyword DECLARE but do not declare anything	Find user-defined routines that use keyword DECLARE but do not declare anything.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 14:47	MIT License	View
934	User-defined routines that use md5 hash for other purposes than generating test data	Find user-defined routines that use md5 hashes for the security purposes. Nowadays such hashes can be calculated too quickly and its use should be avoided at least for hashing passwords. Exclude routines that invoke both md5 function and generate_series function and are thus probably used to generate test data.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-31 15:23	MIT License	View
935	User-defined routines that use positional references to parameters	Use parameter names instead of positional references to improve code evolvability.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 13:18	MIT License	View
936	User-defined routines that use xmin hidden column	Find 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.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 11:30	MIT License	View
937	User-defined routines with dynamic SQL	Find routines that use dynamic SQL. Make sure that dynamic SQL is indeed needed, i.e., the task cannot be solved with static SQL. Make sure that the routine is protected against attacks that use SQL injection method.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 11:09	MIT License	View
938	User-defined routines with dynamic SQL that are potential targets of the SQL injection attack	Find routines that have at least one input parameter, use dynamic SQL but do not escape the input arguments at all.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 11:58	MIT License	View
939	User-defined routines with the same parameters (same name and type) regardless of the order of parameters	Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
940	Username is not unique	Find textual columns that potentially contain usernames (including columns that potentially contain e-mail addresses) that do not have a unique constraint or a unique index that involves only this column.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View