PostgreSQL Query Catalog

Find base table and foreign table columns with the Boolean type that has a CHECK constraint that involves only this column. Avoid unnecessary CHECK constraints. The Boolean type contains only two values and there is nothing to check. By creating a check that determines that possible values in the column are TRUE and FALSE, one duplicates the attribute constraint (column has a type). This is a form of duplication.

CHECK constraints with the cardinality bigger than one that involve the same set of columns. Make sure that there is no duplication.

Find domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains.

Domains are like words that can be used to construct generalized claims about the real world (table predicates). Better not to duplicate the words in the dictionary.

Find cases where a base table has been split horizontally into multiple smaller base tables based on the distinct values in one of the columns of the original table. Each such newly created table has the name, a part of which is a data value from the original tables. Find base tables that have the same columns (column name, column order, data type) and the difference between the tables are the numbers in the table names (table1, table2, etc.).

This query identifies superfluous CHECK constraints where a programmatic length check duplicates a declarative, data type-based length limit. For instance, a CHECK constraint like char_length(column) <= 100 on a column already defined as VARCHAR(100) is redundant.

The same table should not have multiple CHECK constraints with exactly the same Boolean expression. 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.

The same domain should not have multiple CHECK constraints with exactly the same Boolean expression. 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 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 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.