| 101 |
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 |
| 102 |
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 |
| 103 |
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 |
| 104 |
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 |
| 105 |
CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that do not explicitly specify a time zone |
This query identifies CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that use timestamp literals with fixed UTC offsets instead of named time zones. This practice is flagged as a design flaw because fixed offsets do not account for Daylight Saving Time (DST), leading to constraints on both single timestamps and range boundaries that are unable to correctly represent a local time zone's rules throughout the entire year. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-19 16:31 |
MIT License |
View |
| 106 |
Inconsistent syntax for whitespace validation constraints |
This query audits the database schema for syntactical inconsistency in CHECK constraints designed to prohibit empty or whitespace-only strings. It identifies the concurrent use of disparate expression patterns—such as column ~ '\S', column !~ '^[[:space:]]*$', or trim(column) <> ''—to achieve the same functional validation. While these patterns may enforce identical logical rules, the lack of a single, standardized idiom increases cognitive load and maintenance complexity. The query facilitates a review to select one preferred syntax and enforce its uniform application across all relevant columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-19 15:00 |
MIT License |
View |
| 107 |
Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) |
This query identifies redundant CHECK constraints by detecting cases of logical equivalence or subsumption on a single column. For instance, a constraint like description !~ '^[[:space:]]*$' logically subsumes a less comprehensive constraint such as description <> '', rendering the latter superfluous. Eliminating such duplication is a best practice that improves schema clarity, reduces maintenance overhead, and removes logical noise. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-19 14:54 |
MIT License |
View |
| 108 |
Redundant trim() function in whitespace constraints |
This query identifies superfluous trim() function calls within CHECK constraints where the validation is performed by a regular expression that disallows whitespace-only strings. A constraint using the pattern column !~ '^[[:space:]]*$' already provides comprehensive validation against empty or whitespace-only strings by anchoring the check to the start (^) and end ($) of the string. The trim() function is a pre-processing step that does not alter the boolean outcome of this specific regex match, making the expression trim(column) !~ '^[[:space:]]*$' functionally equivalent to the simpler column !~ '^[[:space:]]*$'. Removing the unnecessary function call improves clarity and simplifies the constraint. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-17 13:26 |
MIT License |
View |
| 109 |
Names of database objects that perhaps end with a sequence number (3) |
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-17 13:20 |
MIT License |
View |
| 110 |
Names of database objects that perhaps end with a sequence number (aggregate view) |
This query provides a statistical overview of a potential naming convention issue by counting the number of user-defined database object identifiers that terminate in one or two numerical digits. This naming pattern is a design smell, often indicating bad database structure or semantic ambiguity. Instead of listing each individual name, the query returns an aggregate count, which is useful for quickly assessing the overall prevalence of this issue within the schema and tracking remediation progress over time. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-17 13:18 |
MIT License |
View |
| 111 |
Names of database objects that perhaps end with a sequence number (2) |
This query identifies user-defined database object identifiers that terminate in one or two digits. This naming pattern is a design smell, as it often indicates either a bad database structure or the use of "magic numbers" that obscure the identifier's semantic meaning. Database object names should be fully descriptive and self-documenting. The presence of a numerical suffix necessitates a review to determine if a more descriptive name is required (e.g., renaming report_23 to report_for_year_2023) or if the data model needs to be changed. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-17 13:17 |
MIT License |
View |
| 112 |
Names of database objects that perhaps end with a sequence number |
This query identifies user-defined database objects that share a common container and base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2). To avoid false positives—such as domains like d_name_50 and d_name_100 where the number signifies a length—the query employs a specific heuristic. It assumes a sequence starts with 1, 2, and 3. By removing these numbers from object names, it checks if multiple objects of the same type and base name result within the same container. A positive match strongly implies an intentional, sequential numbering. This pattern indicates a denormalized design, which complicates querying and is difficult to scale. The correct approach is to normalize the schema by creating a separate table for the repeating attribute. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-17 13:14 |
MIT License |
View |
| 113 |
Single-column natural primary keys |
This query identifies primary keys that consist of a single column and are not system-generated (i.e., they are not associated with a sequence or defined as IDENTITY columns). This pattern is characteristic of a natural primary key, where the key's value is derived from a real-world, user-defined attribute rather than an arbitrary surrogate value. Identifying these keys is crucial for auditing a data model's key strategy and understanding its reliance on meaningful, potentially mutable, business data for entity identification. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 12:50 |
MIT License |
View |
| 114 |
Inappropriate use of trim function in whitespace constraints |
This query identifies a semantic mismatch between the name and implementation of CHECK constraints (on tables, foreign tables, or domains). It targets constraints whose names suggest they validate against whitespace-only strings (e.g., names containing 'whitespace', 'space', 'blank'), but whose logic inappropriately uses the trim() function. The trim() function is a formatting tool for removing leading/trailing spaces, not a validation tool for ensuring a string is not composed entirely of whitespace. This indicates a likely implementation error, as a more robust regular expression (e.g., column !~ '^\s*$') is the correct tool for this type of validation. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 12:34 |
MIT License |
View |
| 115 |
Perhaps multiple surrogate keys |
This query identifies base tables with a potentially redundant key structure. It specifically targets those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column whose name matches the surrogate key naming convention (id_* or *_id). This pattern is a strong indicator of multiple surrogate keys for the same entity, which can indicate an overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 11:37 |
MIT License |
View |
| 116 |
Multiple simple keys with integer values |
This query identifies base tables with a potentially redundant key structure, specifically those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column. This pattern may suggest the presence of multiple surrogate keys for the same entity, which can indicate overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 11:35 |
MIT License |
View |
| 117 |
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 |
| 118 |
Find pointless regular expressions that match any value |
This query identifies regular expressions that are tautological, specifically those that effectively match any non-NULL string, such as ^.*$. It is superfluous because it evaluates to TRUE for any non-NULL value, including an empty string. It provides no actual data validation and acts as a no-operation (no-op) check. Such patterns are often artifacts of placeholder code, incomplete logic, or a fundamental misunderstanding of regular expression syntax. Removing them improves clarity and eliminates a useless computational step. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 11:16 |
MIT License |
View |
| 119 |
Find mixed usage of \w and [[:alnum:]] regex syntax |
This query audits regular expressions within the database to detect inconsistent syntax for matching alphanumeric characters. It checks for the concurrent use of both the Perl-style shorthand \w and the POSIX character class [[:alnum:]]. While these are often functionally similar, their exact behavior can differ based on locale settings (e.g., \w may include underscores while [[:alnum:]] does not). Using both styles within the same codebase indicates a lack of a clear standard, which can lead to maintainability issues and subtle, locale-dependent bugs. Standardizing on a single, well-understood syntax is recommended for clarity and predictability. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 11:11 |
MIT License |
View |
| 120 |
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 |