| 941 |
Incorrect field size (based on default values) (2) |
This query identifies potential schema discrepancies related to column sizing. It flags text columns in base tables where the static default value is unusually short—specifically, less than half the column's maximum allowed capacity. |
Problem detection |
INFORMATION_SCHEMA only |
2026-05-05 15:16 |
MIT License |
View |
| 942 |
Quoted numbers indicating poor type selection |
This query identifies potential type mismatches by flagging numeric literals enclosed in single quotes. This pattern often triggers unnecessary type casting overhead or indicates a schema design flaw where a textual data type (e.g., VARCHAR) is used to store exclusively numeric data. The recommendation is to either align the data type with the content (e.g., switch to SMALLINT for codes like '1', '2') or ensure the data justifies the textual type by including non-numeric characters. The query explicitly excludes standard SQL error codes found in exception handling routines, as these are syntactically required to be strings. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-19 20:29 |
MIT License |
View |
| 943 |
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 |
| 944 |
Range lower bound restriction does not consider -infinity |
This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their lower bound and an additional CHECK constraint, but this check does not account for -infinity. This may indicate a "magic number" problem, where a fixed lower limit (e.g., '1900-01-01') is used instead of the more explicit and semantically correct unbounded (-infinity) value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |
| 945 |
Range upper bound restriction dos not consider infinity |
This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their upper bound and an additional CHECK constraint, but this check does not account for infinity. This may indicate a "magic number" problem, where a fixed upper limit (e.g., '2900-01-01') is used instead of the more explicit and semantically correct unbounded (infinity) value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |
| 946 |
Unnecessary explicit locking in read-only routines |
This query identifies read-only routines that unnecessarily utilize explicit locking mechanisms. PostgreSQL's Multi-Version Concurrency Control (MVCC) ensures that readers do not block writers (and vice versa) for standard query operations. Therefore, routines that perform no data modification (DML) and do not raise exceptions have no functional need to acquire table-level (LOCK TABLE) or row-level (FOR SHARE/UPDATE) locks. Using them in this context provides no benefit while actively degrading system concurrency by blocking other transactions. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 13:23 |
MIT License |
View |
| 947 |
Recursive triggers potentially causing infinite loops |
This query identifies recursive triggers, which occur when a trigger's execution performs an operation (such as an UPDATE on the same table) that immediately causes the same trigger to fire again. This creates a potential infinite loop of execution. While PostgreSQL implements a stack depth limit to detect and terminate such runaway processes to prevent a complete system crash, relying on this fail-safe is poor engineering practice. These triggers consume significant system resources before failure and invariably result in aborted transactions. They should be refactored to avoid self-invocation. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-14 12:14 |
MIT License |
View |
| 948 |
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 |
| 949 |
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 |
| 950 |
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 |
| 951 |
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 |
| 952 |
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 |
| 953 |
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 |
| 954 |
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 |
| 955 |
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 |
| 956 |
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 |
| 957 |
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 |
| 958 |
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 |
| 959 |
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 |
| 960 |
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 |