| # | Name | Goal | Type | Data source | Last update ▼ | License | |
|---|---|---|---|---|---|---|---|
| 81 | Insufficient number of user-defined base tables | This query performs a basic structural assessment of the database schema by counting the number of user-defined base tables. It verifies whether the data model meets a minimum complexity threshold, requiring the existence of at least 7 distinct base tables. This metric serves as a proxy for the scope and depth of the implemented domain model. | Problem detection | INFORMATION_SCHEMA only | 2025-11-30 09:30 | MIT License | View |
| 82 | Insufficient number of user-defined non-trigger SQL routines with SQL-standard routine body | This query assesses the database's adherence to modern SQL standards regarding procedural code. It verifies whether the schema contains a minimum of 3 user-defined, non-trigger routines that are specifically written in the SQL language and utilize a SQL-standard body (defined using BEGIN ATOMIC ... END). This excludes routines written in PL/pgSQL or those using the legacy string-literal body definition. The metric ensures a baseline adoption of the standardized, portable syntax for SQL routines introduced in newer PostgreSQL versions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-30 09:22 | MIT License | View |
| 83 | Insufficient number of user-defined non-trigger routines | This query performs a quantity-based assessment of the database's procedural logic. It verifies whether the schema meets a minimum complexity requirement by counting the number of user-defined, non-trigger routines (functions and procedures). The check passes only if the count is equal to or greater than the threshold of 4. This metric is used to ensure a baseline level of backend logic implementation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-30 08:47 | MIT License | View |
| 84 | User-defined non-trigger SQL and PL/pgSQL routines | This query retrieves a comprehensive list of user-defined routines (functions and procedures) written in SQL or PL/pgSQL. It explicitly filters the result set to exclude:
The result provides an inventory of the application's explicit, callable database logic. |
General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-30 08:41 | MIT License | View |
| 85 | Intra-object inconsistency in string concatenation methods | This query detects internal inconsistency within individual database objects (user-defined routines, views, materialized views). It flags objects that utilize both the standard concatenation operator (||) and variadic concatenation functions (concat() or concat_ws()) within the same definition body. Mixing null-unsafe operators (||) with null-safe functions (concat) in a single routine suggests a lack of coherent logic or an incomplete refactoring effort, potentially leading to confusing behavior regarding NULL handling. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-27 12:27 | MIT License | View |
| 86 | Find || operations missing coalesce() protection | This query identifies potential null-propagation defects in user-defined routines and views. It targets subqueries utilizing the standard concatenation operator (||) where operands are not protected by a coalesce() function. In PostgreSQL, the operation string || NULL yields NULL, causing the entire result to vanish if any component is missing. This behavior is often unintentional. The query flags these risky patterns, suggesting remediation via explicit null handling or the adoption of null-safe alternatives like concat(), concat_ws(), or format(). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-27 11:50 | MIT License | View |
| 87 | Base table columns with the same name have different types | This query identifies semantic inconsistencies across the database schema by finding base table columns that share the same identifier (name) but are defined with differing data types. According to standard data modeling principles, a shared attribute name implies a shared domain concept (e.g., status_code should consistently be an SMALLINT or a CHAR). Discrepancies in data types for homonymous columns (e.g., is_active being BOOLEAN in one table and SMALLINT in another) hinder interoperability, complicate join logic, and confuse developers. | Problem detection | INFORMATION_SCHEMA only | 2025-11-27 11:20 | MIT License | View |
| 88 | Snake_case violations detected by common suffixes | This query identifies database identifiers (columns, parameters, etc.) that likely violate the snake_case naming convention based on suffix analysis. It flags names ending with common temporal or attributional terms (date, time, by) where the suffix is not immediately preceded by an underscore. This pattern is highly indicative of camelCase (e.g., createdDate, updatedBy) or PascalCase usage. Adhering to snake_case (e.g., created_date, updated_by) is the recommended standard for SQL database schemas to ensure consistency and readability. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-27 11:13 | MIT License | View |
| 89 | Phone number columns lacking digit validation constraints | This query identifies non-foreign key base table columns intended for telephone number storage that lack essential data validation. It targets columns whose names imply phone data (e.g., containing "phone", "tel") but which have no associated simple CHECK constraint validating the presence of numeric digits. Without such a constraint (e.g., a regex check for [0-9]), the column allows for invalid entries such as purely alphabetic strings or email addresses, compromising data integrity. The query assumes that a valid phone number must minimally contain digits. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-27 10:39 | MIT License | View |
| 90 | Semantic mismatch: non-textual data types for phone numbers | This query identifies a semantic mismatch in data type selection for columns intended to store telephone numbers. It flags columns whose identifiers imply phone number content (e.g., names containing "phone", "mobile", "telef") but are defined with non-textual data types (e.g., INTEGER, NUMERIC, BIGINT). Telephone numbers are semantically strings, as they may contain leading zeros, international prefixes (+), and formatting characters (-, (), ext.), and are not subject to arithmetic operations. Storing them as numeric types leads to data loss (truncation of leading zeros) and formatting inflexibility. | Problem detection | INFORMATION_SCHEMA only | 2025-11-27 10:35 | MIT License | View |
| 91 | The same CHECK has a different name in different places (2) | This query audits the database schema to enforce a uniform naming strategy for CHECK constraints. It identifies inconsistencies where constraints enforcing identical Boolean expressions are named using disparate patterns across different tables. To isolate the naming pattern from specific object identifiers, the query normalizes the constraint names by substituting the actual table name with the generic token TABLE. This allows it to detect violations of the "Clean Code" principle of consistency—flagging cases where the same logical rule is implemented with a specific naming convention in one context (e.g., chk_TABLE_column) but a different convention in another (e.g., TABLE_column_check). | Problem detection | system catalog base tables only | 2025-11-27 10:17 | MIT License | View |
| 92 | Very similar column names | This query performs an intra-table analysis to detect potential schema ambiguities. It identifies pairs of columns within the same table that exhibit both high textual similarity and structural equivalence. Specifically, it flags pairs where the names have a Levenshtein edit distance of exactly one and the columns share the same data type or domain. This combination suggests a high probability of typographical errors (e.g., status vs statuss), inconsistent naming (singular vs. plural), or improper denormalization (e.g., item1 vs item2), all of which undermine schema clarity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-25 17:32 | MIT License | View |
| 93 | Very similar (but not equal) routine names | This query audits the schema for semantic ambiguity by identifying pairs of routine names (functions, procedures) that exhibit high textual similarity but are not identical. It filters for name pairs with a Levenshtein edit distance of exactly one (less than two, but excluding equality). This specific filter targets typographical errors (e.g., calc_tax vs. calc_tux) or inconsistent singular/plural naming (e.g., get_user vs. get_users), while correctly ignoring valid method overloading where names are identical. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-25 17:29 | MIT License | View |
| 94 | Very similar table names | This query identifies potential redundancy or ambiguity in the schema by detecting pairs of table names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters (i.e., a distance of 0 or 1). This check applies across different types of tables (base tables, foreign tables, derived tables), helping to uncover typographical errors (e.g., users vs user), inconsistent pluralization, or confusingly named entities that violate the principle of distinct and descriptive identifiers. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-25 17:24 | MIT License | View |
| 95 | Inconsistent digit character class syntax in regular expressions | This query audits regular expressions within the database to detect syntactical inconsistencies in identifying numeric digits. It checks for the concurrent use of disparate character class notations: range-based ([0-9]), Perl-style shorthand (\d), and POSIX character classes ([[:digit:]]). While these are often functionally equivalent for standard ASCII digits, mixing multiple syntaxes within a single codebase indicates a lack of standardization, which reduces code readability and increases cognitive load during maintenance. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-25 17:13 | MIT License | View |
| 96 | Routines that use old syntax for limiting rows | This query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-21 17:37 | MIT License | View |
| 97 | Inconsistent regex character class syntax usage | This query audits regular expressions across the database to detect inconsistent syntax when defining character classes. It specifically checks for the concurrent usage of Perl-style shorthand notation (e.g., \s, \d) and POSIX character classes (e.g., [[:space:]], [[:digit:]]). While often functionally overlapping, these syntaxes may have subtle behavioral differences depending on locale and engine versions. The presence of both styles within a single database indicates a lack of coding standards, reducing readability and increasing maintenance complexity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-20 13:12 | MIT License | View |
| 98 | Find inconsistency in double underscore/space usage | This query audits the database schema for inconsistency regarding the use of consecutive separators (double underscores or spaces) within identifiers. It groups objects by type (e.g., CHECK constraints, indexes) and identifies categories where a mixed naming convention exists—specifically, where some identifiers utilize consecutive separators (e.g., idx__name) while others of the same type do not (e.g., idx_name). This variation suggests a lack of enforced coding standards, leading to unpredictability in the schema. The query facilitates a review to establish and enforce a single, uniform naming convention. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-20 13:08 | MIT License | View |
| 99 | Row-level BEFORE triggers on base tables with RETURN NULL cancellation logic | This query identifies row-level BEFORE triggers on base tables that execute a RETURN NULL statement without raising a corresponding exception. In PostgreSQL, returning NULL from a BEFORE trigger silently aborts the pending INSERT, UPDATE, or DELETE operation for the current row. Unlike an exception, which alerts the calling application to the failure, a silent cancellation allows the transaction to proceed as if successful, but with the data modification discarded. This behavior is often unintentional (e.g., a forgotten RETURN NEW) and poses a significant risk of data loss and difficult-to-debug application logic errors. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-20 12:29 | MIT License | View |
| 100 | Base table columns permitting empty strings and strings that consist of only whitespace characters (2) | This query identifies non-foreign key columns with a textual data type that lack essential validation. It specifically targets columns that are missing both of the following fundamental checks:
The absence of such comprehensive validation increases the risk of poor data quality and potential application-level bugs. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-20 12:20 | MIT License | View |