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	...
581	Routines without an action	Find routines that body does not contain any action.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 12:14	MIT License	View
582	Sorting rows based on random values in routines without limiting rows	Find routines that contain a statement that sorts rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 16:57	MIT License	View
583	Sorting rows based on random values in routines	Find routines that contain a statement that sorts rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 16:56	MIT License	View
584	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
585	Polymorphic routines	Find routines that have a parameter (input or output) that can have values from different types. This parameter has one of the PostgreSQL polymorphic types. The set of polymorphic types in PostgreSQL is a proper subset of its pseudo-types. The use of such a parameter allows a single routine definition to operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 13:12	MIT License	View
586	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
587	Routine for reading data uses another routine to read some data	Find routines that only read data but invoke some other routine to read some more data.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-10 17:10	MIT License	View
588	Meaningless terms in routines	Find routines that subquery contains terms "foo", "bar", "foobar", or "baz".	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 14:09	MIT License	View
589	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
590	FOR UPDATE is not needed if there is no FROM clause in the SELECT statement	Find routines that use SELECT … FOR UPDATE without selecting rows from a specific table. For instance: SELECT 'text' AS v FOR UPDATE;	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 14:10	MIT License	View
591	Perhaps inconsistent use of temporal functions	Find routines that use temporal functions CURRENT_TIMESTAMP, LOCALTIMESTAMP, or now() that is inconsistent with the default values of the columns that are used by the routine, e.g., function uses a column with the default value LOCALTIMESTAMP but the routine uses function CURRENT_TIMESTAMP or now().	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-10 17:11	MIT License	View
592	Do not refer to the table schema in the references to columns	Find routines where in SELECT or UPDATE statements references to columns are prefixed with references to the table schema. Referring to schema in this context bloats the code.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 17:32	MIT License	View
593	Routine body with ordering the query result based on positional references	Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-31 15:23	MIT License	View
594	Routines that can be invoked with a variable number of arguments	Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:37	MIT License	View
595	Prefer Polymorphism to If/Else or Switch/Case	Find routines with IF/ELSE or SWITCH/CASE statements. If your routine has a multipart IF/CASE statement, then perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-29 13:31	MIT License	View
596	Prefer Polymorphism to If/Else or Switch/Case (2)	Find routines with multiple raise exception commands. Perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-29 13:39	MIT License	View
597	Perhaps updating of modification time is missing	Find routines with SQL-standard body that seem to update data in a table that has a column for modification time but the routine does not seem to update the modification time while updating the row.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-24 12:59	MIT License	View
598	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
599	Perhaps a reference to the variable OLD is missing	Find row level before delete triggers that only task is not to raise an exception and where the variable OLD is not used in the trigger routine outside the RETURN clause.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-21 19:29	MIT License	View
600	Perhaps a reference to the variable NEW is missing	Find row level before insert and before update triggers that only task is not to raise an exception and where the variable NEW is not used in the trigger routine outside the RETURN clause.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-21 20:34	MIT License	View