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	...
561	Inconsistency of using parameter data types	Find parameters of routines that have the same name but a different type. Parameters that have the same name should have, in general, the same data type as well, assuming that the routines, which have the parameters, have different names, i.e., there is no overloading in play.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
562	All parameters with DEFAULT values	Find parameters of user-defined routines that have a default value.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
563	Paramtetes with an array type, XML, JSON, or JSONB type	Find parameters of user-defined routines that type is an array type, xml, json, or jsonb type. Make sure that the parameter name matches the type (perhaps should be in plural).	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:21	MIT License	View
564	Parameter name contains the routine name	Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 18:03	MIT License	View
565	Parameter name is the same as the routine name	Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 18:03	MIT License	View
566	Partial or case insensitive unique indexes	Find partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
567	All declaratively partitioned tables	Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 15:13	MIT License	View
568	Too many slashes in regular expressions	Find patterns of regular expressions where more than \ is written instead of \, e.g., \\s is used instead of \s to refer to a character class.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 11:15	MIT License	View
569	Patterns of the Boolean expressions of simple CHECK constraints	Find patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)	General	INFORMATION_SCHEMA+system catalog base tables	2022-11-13 16:06	MIT License	View
570	Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column	Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:52	MIT License	View
571	Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with two or three columns	Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:52	MIT License	View
572	PL/pgSQL functions with consecutive RETURN clauses	Find PL/pgSQL functions with consecutive RETURN clauses. Only the first RETURN will be used, others are unnecessary.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-12-16 11:38	MIT License	View
573	Routines that use old syntax for limiting rows	Find PL/pgSQL routines and SQL routines that do not have SQL-standard body that use unstandardized LIMIT clause instead of standardized FETCH FIRST n ROWS clause. The query excludes routines that are a part of an extension.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-10-29 11:34	MIT License	View
574	Perhaps unnecessary DECLARE section in a PL/pgSQL routine (2)	Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the keyword DECLARE is followed by BEGIN, i.e., the DECLARE section is empty.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-23 13:39	MIT License	View
575	Perhaps unnecessary DECLARE section in a PL/pgSQL routine	Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the only task seems to be raising an exception. The query excludes the cases where the error message is constructed dynamically, i.e., in this case using a variable maybe justifiable.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-30 10:19	MIT License	View
576	Incorrect comparison operator	Find PL/pgSQL routines that use comparison operators =< or =>.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-11 14:50	MIT License	View
577	PL/pgSQL routine with plain SELECT	Find PL/pgSQL that contain a SELECT statement that is not a SELECT … INTO statement. This is not permitted in PL/pgSQL routines.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 17:07	MIT License	View
578	Sequence generators not needed	Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-18 10:00	MIT License	View
579	Simple natural primary keys	Find primary keys that consist of one column and that values are not generated by the system.	General	INFORMATION_SCHEMA+system catalog base tables	2021-03-08 00:47	MIT License	View
580	Definition of a non-minimal superkey instead of a candidate key (based on key constraints)	Find primary/key unique constraints (sets of columns) that are proper subsets of other primary key/unique constraints of the same table. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys, i.e., the keys should not have redundancy in terms of columns.	Problem detection	system catalog base tables only	2021-10-16 10:29	MIT License	View