| # | Name | Goal â–˛ | Type | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 961 | Redundant leading or trailing wildcards in regular expressions | This query identifies regular expressions that contain superfluous .* constructs at the beginning or end of the pattern. In standard unanchored regular expression matching (as performed by PostgreSQL's ~ operator), the engine checks for the pattern's existence anywhere within the string by default. Therefore, a leading or trailing .* is typically redundant, as it explicitly instructs the engine to do what it would already be doing implicitly. Removing these unnecessary wildcards improves the readability and maintainability of the expression and can, in some cases, lead to better performance by reducing unnecessary backtracking. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 10:04 | MIT License | View |
| 962 | Gratuitous context in the names of schema objects | This query identifies schema objects with names that are redundantly prefixed with their own schema's name. It flags any object whose name begins with the schema name plus at least one other character. This enforces the design principle that a schema is a sufficient namespace, and therefore, objects within it do not require the additional, repetitive context in their own names. | Problem detection | system catalog base tables only | 2025-11-07 10:12 | MIT License | View |
| 963 | No-operation routines with static return values | This query identifies SQL routines that are functionally equivalent to a no-operation (no-op) instruction, meaning their sole operation is to return either a constant literal or an unmodified input parameter. Such routines provide no transformation or logic. They are typically superfluous and may represent placeholder code from early development, refactoring artifacts where original logic was deprecated, or simple logical oversights. Eliminating these functions reduces code clutter, simplifies application logic, and removes a marginal but unnecessary layer of computational overhead. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 11:06 | MIT License | View |
| 964 | Routines with non-deterministic side effects and static return values | This query identifies SQL routines that exhibit a dangerous combination of state-changing side effects (DML) and a static return value (either a constant literal or an unmodified input parameter). The function's name and signature often imply that the return value is the result of its operations (e.g., a new balance, a generated ID). However, the static return value contradicts this, creating a semantic disconnect between the routine's name and its contract. This is a significant design flaw that can lead to subtle but critical bugs, as the calling code may act on a return value that does not accurately reflect the database state after the routine's execution. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 10:59 | MIT License | View |
| 965 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) (2) | This query identifies superfluous CHECK constraints by detecting logical subsumption. It targets columns where a general non-blankness constraint is made redundant by a more specific, format-validating constraint. For instance, if an e_mail column is validated by a format constraint from Set1 (e.g., e_mail LIKE '%@%'), that constraint implicitly ensures the string is not blank. Therefore, any co-existing constraint from Set2 (e.g., e_mail !~ '^[[:space:]]*$') is logically unnecessary and can be removed to reduce schema complexity. Example. Set1: {e_mail~'[[:alnum:]@]+'; position('@' in e_mail)>0; e_mail LIKE '%@%'} Set2: {e_mail~'\S'; e_mail!~'^[[:space:]]*$'; e_mail!~'^\s*$'} If column e_mail has a constraint from Set1, then it does not need a constraint from Set2. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-14 19:33 | MIT License | View |
| 966 | Double checking of the maximum character length | 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:14 | MIT License | View |
| 967 | Unique constraints made redundant by an exclude constraint | This query identifies superfluous UNIQUE constraints where the constraint is logically subsumed by a more general EXCLUDE constraint on the same table. It targets cases where the set of columns in a UNIQUE or PRIMARY KEY constraint is a subset of (or equal to) the columns in an EXCLUDE constraint, provided the EXCLUDE constraint uses the equality operator (=) for those same columns. In this scenario, the EXCLUDE constraint already enforces uniqueness as part of its more complex logic, rendering the separate UNIQUE constraint redundant. Eliminating this duplication improves schema clarity and removes an unnecessary constraint check. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 09:56 | MIT License | View |
| 968 | Perhaps an unnecessary default value (the empty string or a string that consists of only whitespace) of a base table column/domain | This query identifies table columns and domains that are configured with a semantically void DEFAULT value. It specifically flags defaults that are an empty string ('') or a string consisting solely of whitespace characters (e.g., spaces, newlines). This practice is a design flaw because it automatically populates the database with non-substantive data, which can lead to application-level bugs when code does not explicitly check for such "blank" values in addition to NULL. | Problem detection | INFORMATION_SCHEMA only | 2025-11-12 15:02 | MIT License | View |
| 969 | Base tables that have a surrogate key and do not have any uniqueness constraints | This query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 15:29 | MIT License | View |
| 970 | Too generic names (tables) | This query identifies tables with semantically weak, generic names that violate schema design best practices. It flags tables with name components such as "table", "data", "information", or "list". The principle is that a table name should accurately represent the real-world entity it models. Using generic nouns obscures the schema's meaning, reduces readability, and forces developers to inspect the table's contents to understand its purpose. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 14:10 | MIT License | View |
| 971 | Perhaps an unsuitable use of CHAR(n) type in base tables (based on names) | This query identifies the semantic misuse of the CHAR(n) data type for non-foreign key columns where n > 1. It operates on a heuristic, flagging columns whose names suggest they store variable-length data (e.g., "name", "comment", "description", "email") rather than genuinely fixed-length data like standardized codes or hash values. Because CHAR(n) is a fixed-width, space-padded type, its use for variable-length strings is inefficient in terms of storage and can introduce application-level logic errors, making VARCHAR(n) the appropriate alternative. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 15:08 | MIT License | View |
| 972 | Names of database objects that perhaps end with a sequence number (2) | This query identifies user-defined database objects that share a common container and a common base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2 in the same table or tables address1 and address2 in the same schema). Such a structure complicates querying (e.g., requiring checks across multiple columns or tables) and is difficult to scale. The correct approach is for example to create a separate table for the repeating attribute, establishing a one-to-many relationship with the parent table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 09:52 | MIT License | View |
| 973 | Is does not return a boolean | This query identifies user-defined SQL and PL/pgSQL routines (excluding triggers) that exhibit a semantic inconsistency between their name and return type. It flags non-trigger routines whose names begin with a predicate prefix (e.g., is_, has_, can_, on_) but whose defined return type is not BOOLEAN. Such a mismatch violates the principle of self-documenting code, as the name creates a strong expectation of a TRUE/FALSE return value, which the routine's signature then contradicts. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 14:03 | MIT License | View |
| 974 | Frequency of table name lengths based on the table type | This query provides a statistical analysis of identifier length across the schema. It calculates a frequency distribution by grouping base tables, views, and materialized views based on the character length of their names. The result is a count of how many objects exist for each distinct name length, which can be used to audit naming conventions or identify outliers. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 12:56 | MIT License | View |
| 975 | All key constraints | This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table. | General | system catalog base tables only | 2025-11-15 11:24 | MIT License | View |
| 976 | Names of columns with the type BOOLEAN | This query retrieves the names of all columns defined with the BOOLEAN data type to facilitate an audit of naming consistency. The primary objective is to verify adherence to a recommended best practice: boolean column names should be prefixed with a semantic predicate, such as is_ (in English) or on_ (in Estonian). This convention enhances the self-documenting nature of the schema and improves the readability of SQL statements by framing the column's purpose as a true/false question. | General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 14:08 | MIT License | View |
| 977 | Inconsistent use of gratuitous context in the names of non-foreign key and non-primary key columns | This query validates column naming conventions. It returns a row only for tables that have an inconsistent mix of column naming styles—specifically, where some columns (that are not part of a primary or foreign key) are prefixed with the table name and others are not. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:12 | MIT License | View |
| 978 | Perhaps too many input parameters | Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 979 | Wrong comment - trigger function does not implement a database operation | Trigger functions should not contain references to database operations. Perhaps the trigger implements ensuring some invariant of the operation but it does not implement the operation itself. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 980 | The number of user-defined triggers by schema, by type, and in total | Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |