PostgreSQL Query Catalog

Find base table columns that have both default value determined through a domain and default value that is directly attached to the column. Do not duplicate specifications of default values to avoid confusion and surprises. If column and domain both have a default value, then in case of inserting data the default value that is associated directly with the column is used.

Find domains that have the same properties (base type, character length, not null + check constraints, default value, collation). There should not be multiple domains that have the same properties. Do remember that the same task can be solved in SQL usually in multiple different ways. Therefore, the domains may have syntactically different check constraints that solve the same task. Thus, the exact copies are not the only possible duplication.

Find enumerated types with exactly the same values. There should not be multiple types that have the same values.

Find duplicate foreign key constraints, which involve the same columns and refer to the same set of columns.

Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication.

Find completely overlapping key (primary key, unique, and exclude where all operators are =) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns.

Find materialized views with exactly the same subquery. There should not be multiple materialized views with the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

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 columns that have NOT NULL constraint through a domain and also directly. Do not duplicate NOT NULL constraints in orde to avoid confusion and surprises.

Find derived tables (views and materialized views) that contain both DISTINCT and GROUP BY. Make sure that the means for removing duplicate rows from the query result are not duplicated.

Find multiple rules with the same definition (event, condition, action) on the same table. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find regular expressions where within the same specification of a character class the character class alnum as well as 0-9, \d, A-Z, or a-z has been defined.

Find base tables that have more than one stored generated column with the same expression. The support of generated columns was added to PostgreSQL 12. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find cases where the same table has multiple triggers with the same type (row-level, statement-level) that react to the same event with the same WHEN condition and with the same way (by invoking the same function).

Find user-defined routines with the exact duplicate body and parameters. There should not be multiple routines with exactly the same body and parameters (name, type). Having such duplicates is redundancy. Do remember that the same task can usually be solved in multiple different ways. Thus, the exact copies of routine bodies are not the only possible duplication. Moreover, it could be that different routines that solve the same task have different parameter names (but the parameters have the same types, ordinal positions, default values or the routines have different order of parameters). Thus, the query does not find all the duplications.

Find views with exactly the same subquery. There should not be multiple views with exactly the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i).

Find duplicate constraints, which make it more difficult to maintain the constraints. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find duplication of simple CHECK constraints on the same base table or foreign table column. Duplication of the same constraint means that if one starts to manage the code, then changes have to be made in multiple places. The problem is essentially similar with the data redundancy problem that database normalization tries to reduce. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find as to whether the relationships between tables form a complete bidirected graph. Tables are vertices in the graph. There is a directed edge between two vertices if one of the tables refers to another through foreign key relationship on mandatory columns.