| # | Name | Goal | Type | Data source | Last update ▼ | License | |
|---|---|---|---|---|---|---|---|
| 121 | 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 |
| 122 | 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 |
| 123 | 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 |
| 124 | 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 |
| 125 | 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 |
| 126 | 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 |
| 127 | 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 |
| 128 | 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 |
| 129 | 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 |
| 130 | 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 |
| 131 | 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 |
| 132 | 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 |
| 133 | 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 |
| 134 | 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 |
| 135 | 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 |
| 136 | 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 |
| 137 | Insufficient number of user-defined domains | This query assesses the utilization of user-defined domains within the database schema. It verifies a specific structural requirement: the database must contain at least one user-defined domain that is referenced by at least two distinct non-foreign key columns in base tables. This metric serves as an indicator of proper domain reuse and data type standardization. The query validates whether the schema design effectively leverages domains to enforce consistent data definitions across multiple attributes. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-20 11:47 | MIT License | View |
| 138 | Updatable foreign tables that refer to another PostgreSQL table | This query identifies foreign tables established via the postgres_fdw (PostgreSQL Foreign Data Wrapper) that are configured to permit data modification (updatability). While postgres_fdw supports INSERT, UPDATE, and DELETE operations on remote tables, enabling this capability introduces complexity regarding distributed transactions, performance, and security. The query serves as an audit tool to verify that the updatability of these foreign tables is a deliberate architectural requirement and not an unintended default configuration. | General | INFORMATION_SCHEMA only | 2025-11-20 11:45 | MIT License | View |
| 139 | Row-level triggers with RETURN NULL cancellation logic | This query identifies row-level BEFORE and INSTEAD OF triggers that explicitly RETURN NULL. In PostgreSQL's trigger execution model, this return value acts as a cancellation signal. For BEFORE triggers on tables, it aborts the operation for the current row, preventing the INSERT, UPDATE, or DELETE and suppressing subsequent triggers. For INSTEAD OF triggers on views, it signals that no modification was performed. While this behavior can be used for conditional logic (e.g., silently ignoring invalid rows), it presents a risk of unintended data loss or logic errors if used incorrectly. These triggers should be audited to ensure the cancellation behavior is intentional and correctly implemented. | General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-20 11:41 | MIT License | View |
| 140 | Simplify regex by combining alpha and digit classes | This query identifies regular expressions that can be simplified by consolidating separate character class references. It specifically targets patterns that explicitly match both alphabetic characters ([:alpha:]) and numeric digits ([:digit:], \d, or [0-9]) as separate components within a larger character set (e.g., [[:alpha:][:digit:]]). These distinct classes can be refactored into the single, more concise POSIX character class [:alnum:], which logically represents the union of both. Performing this simplification improves the readability and compactness of the regular expression without altering its behavior. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-19 17:38 | MIT License | View |