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	...
981	TG_ARGV is missing	Write correct code. If you pass arguments to a trigger function, then the function should use the arguments. TG_ARGV[]: "Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value." (PostgreSQL documentation)	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
982	STATEMENT level triggers and ROW level AFTER triggers without RETURN NULL	Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation)	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
983	CHECK constraint cardinality is zero	Write correct constraints. Usually the constraint expression should refer to at least one column. A domain constraint expression should refer to the stub VALUE. For instance, the constraint CHECK(1=0) that is associated with a table T would prevent adding any rows to T. The value of the Boolean expression of this constraint is always FALSE.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-04-22 17:06	MIT License	View
984	Non-predefined character classes must not be between double square brackets	Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-13 12:32	MIT License	View
985	Unbalanced brackets	Write expressions correctly. Find code fragments that have unbalanced brackets, i.e., the number of opening brackets is not the same as the number of closing brackets.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-24 16:02	MIT License	View
986	search_path should not be between quotation marks	Write security definer functions securely. Give to the DBMS correctly information about the sequence of schemas that constitute the search path. You shouldn't write search path value between quotation marks or apostrophes. Thus, instead of writing SET search_path = "public, pg_temp"; or SET search_path = 'public, pg_temp'; you should write SET search_path = public, pg_temp;	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-03 21:10	MIT License	View
987	Procedures cannot have START TRANSACTION and SAVEPOINT	You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 11:50	MIT License	View
988	SECURITY DEFINER procedures cannot end transactions	You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:12	MIT License	View
989	Explicit locking is probably not needed	You do not need explicit locking (LOCK TABLE or SELECT … FOR UPDATE) in case of routines that only search some data but do not modify any data and do not raise any exception.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:08	MIT License	View
990	Insufficient view privileges	You must give privileges to use views to the users/roles that correspond to applications	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
991	Insufficient routine privileges	You must give rights to use routines to the users/roles that correspond to applications.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
992	Table privileges have been granted to PUBLIC	You should follow the principle of least privilege and thus not have in your database tables that usage privileges are granted to the pseudo-role PUBLIC, i.e., to all the database users now and in the future.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
993	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
994	LEAKPROOF routines that are perhaps not leakproof	You should not give wrong information to the database management system.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 14:12	MIT License	View
995	The total size of all indexes (system catalog excluded)		Sofware measure	system catalog base tables only	2023-05-14 11:04	MIT License	View
996	The total size of all tables (system catalog excluded)		Sofware measure	system catalog base tables only	2023-05-14 11:04	MIT License	View