PostgreSQL Query Catalog

Find optional composite foreign keys that do not have MATCH FULL specified. Without MATCH FULL the system will permit partial foreign key values

Find pairs of non-function based unique indexes that cover the same set of columns. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. For instance, it helps us to find unique indexes that have been defined to already unique columns.

Find pairs of non-partial unique indexes where the columns of a index are a proper subset of the columns of another index. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude the pairs where both participants have been created to support a constraint. 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 keys based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Defining a unique index essentially means defining a key in the table but it is done at the lower level of abstraction.

Find pairs of tables that have both a foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables.

Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables.

Find parameter names in case of which the same routine has another parameter with the same mode but with more specific name, i.e., the name contains the parameter name in the end or in the beginning.

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.

Find parameters of user-defined routines that have a default value.

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).

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.

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.

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.

Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.

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.

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)

Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.

Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.

Find PL/pgSQL functions with consecutive RETURN clauses. Only the first RETURN will be used, others are unnecessary.

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.

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.