| 941 |
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 |
| 942 |
Unnecessary use of Coalesce |
This query identifies redundant null-handling logic within string manipulation expressions. It targets the use of explicit coalesce() calls nested inside functions that are already null-safe, such as concat(), concat_ws(), or format(). Since these functions implicitly treat NULL arguments as empty strings (or ignore them), wrapping arguments in coalesce(arg, '') is superfluous. The query deliberately excludes expressions using the standard concatenation operator (||), as coalesce() is legitimately required in that context to prevent null propagation. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-03 15:58 |
MIT License |
View |
| 943 |
Redundant DO INSTEAD NOTHING rules on naturally non-updatable views |
This query identifies redundant rewrite rules within the database schema. It targets views that are inherently non-updatable (due to the presence of aggregates, joins, or set operations) but are nevertheless defined with a DO INSTEAD NOTHING rule. Since the PostgreSQL engine cannot perform DML operations on such views natively, the view is effectively read-only by definition. Consequently, the explicit rule serves no functional purpose in preventing data modification and represents superfluous schema metadata. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-26 09:52 |
MIT License |
View |
| 944 |
Chains of ON DELETE CASCADE |
This query identifies referential paths (chains of foreign key relationships) where every constraint in the path is configured with ON DELETE CASCADE. The analysis is intended to uncover potential transitive deletion risks, where a single DELETE operation on a root table could trigger a catastrophic, cascading data loss across multiple, deeply-nested tables. The length of such chains is a key indicator of architectural fragility. |
General |
system catalog base tables only |
2025-11-08 10:50 |
MIT License |
View |
| 945 |
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 |
| 946 |
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 |
| 947 |
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 |
| 948 |
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 |
| 949 |
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 |
| 950 |
Row level triggers that update or delete data |
This query identifies row-level triggers that contain UPDATE or DELETE statements within their execution body. Embedding data modification logic directly within row-level triggers introduces implicit side effects that can complicate transaction management and debugging. Furthermore, this pattern significantly increases the risk of causing cascading trigger chains or infinite recursion loops, potentially degrading system performance and stability. Such logic should be carefully audited to ensure it is strictly necessary and correctly implemented. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-14 12:20 |
MIT License |
View |
| 951 |
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 |
| 952 |
Unique index definition instead of a key declaration |
This query identifies semantic abstraction violations where data uniqueness is enforced via low-level CREATE UNIQUE INDEX statements instead of declarative PRIMARY KEY, UNIQUE, or EXCLUDE constraints. It specifically targets "plain" unique indexes (excluding partial or expression-based indexes) that are functionally identical to standard constraints. According to the ANSI-SPARC architecture, indexes belong to the internal (physical) schema, while constraints belong to the conceptual schema. Therefore, defining business rules using high-level constraint syntax is preferred for semantic clarity and architectural correctness. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-01-19 14:37 |
MIT License |
View |
| 953 |
CHECK constraints on TIMESTAMP and DATE columns that explicitly specify a time zone |
This query identifies semantic anomalies in CHECK constraints applied to columns defined as DATE or TIMESTAMP (without time zone). It flags constraints that incorporate time zone conversion logic (e.g., using AT TIME ZONE). Since these data types store "naive" values devoid of time zone offsets, attempting to apply time zone logic makes the constraint's outcome dependent on the current session or server configuration. This non-deterministic behavior is a design flaw, as data validity should be intrinsic and immutable, not dependent on the environment. |
Problem detection |
INFORMATION_SCHEMA only |
2025-12-14 12:48 |
MIT License |
View |
| 954 |
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 |
| 955 |
Textual code columns lacking specific pattern validation |
This query identifies semantic validation gaps in textual columns intended to store structured codes. It targets non-foreign key columns whose identifiers imply a specific format (e.g., containing the word "code"), but which lack adequate constraints to enforce that format. Specifically, it flags columns that have either no CHECK constraints at all, or only trivial constraints that prohibit empty/whitespace strings. Since "codes" typically adhere to a strict pattern (e.g., fixed length, specific character set), relying solely on a non-empty check is considered insufficient for data integrity. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-29 13:14 |
MIT License |
View |
| 956 |
Data type mismatch in check constraints |
This query identifies single-column CHECK constraints where the validation logic utilizes operators or functions that are incompatible with the column's native data type. It detects cases where the database must perform implicit casting to evaluate the expression (e.g., performing arithmetic on a TEXT column or string manipulation on a DATE column). Relying on implicit coercion in constraints involves unnecessary computational overhead and frequently indicates a fundamental error in data modeling or constraint formulation. |
Problem detection |
system catalog base tables only |
2025-12-05 19:23 |
MIT License |
View |
| 957 |
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 |
| 958 |
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 |
| 959 |
Optimistic locking routines lacking execution feedback |
This query identifies SQL routines that implement optimistic concurrency control via the xmin system column but fail to provide an execution status to the invoker. Specifically, it flags functions that perform UPDATE or DELETE operations filtered by xmin (a version check) but do not return information regarding the operation's success (e.g., a row count or a BOOLEAN status). This is a critical logic flaw; if a concurrency conflict occurs (the row was modified by another transaction), the operation yields zero rows. Without a return value, the failure is silent, leaving the calling application unaware that the data modification did not occur. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-15 11:35 |
MIT License |
View |
| 960 |
Trying to lock a value instead of a row |
This query identifies SQL routines that utilize explicit row locking clauses (e.g., FOR UPDATE, FOR SHARE) in queries that do not target a specific base table or relation. For instance, a statement like SELECT 'text' AS v FOR UPDATE attempts to apply a lock to a scalar constant. Since row-level locks in PostgreSQL require a physical row version (tuple) within a table to be effective, such statements are semantically void. They indicate a fundamental misunderstanding of the concurrency control mechanism and should be corrected to target actual table rows. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 13:00 |
MIT License |
View |