| # | Name | Goal ▲ | Type | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 961 | Invalid explicit locking with aggregate functions | This query identifies SQL statements that attempt to apply explicit row locking (e.g., FOR SHARE, FOR UPDATE) to the result of an aggregate function (e.g., COUNT(*)). This is a semantic error because locking clauses operate on specific physical rows, whereas aggregate functions return a derived scalar value that is decoupled from the underlying row versions. To correctly enforce a lock, the query must select the specific columns (typically the primary key) of the target rows, rather than a computed aggregate. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-10 12:59 | MIT License | View |
| 962 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) (2) | This query identifies superfluous CHECK constraints by detecting logical subsumption. It targets columns where a general non-blankness constraint is made redundant by a more specific, format-validating constraint. For instance, if an e_mail column is validated by a format constraint from Set1 (e.g., e_mail LIKE '%@%'), that constraint implicitly ensures the string is not blank. Therefore, any co-existing constraint from Set2 (e.g., e_mail !~ '^[[:space:]]*$') is logically unnecessary and can be removed to reduce schema complexity. Example. Set1: {e_mail~'[[:alnum:]@]+'; position('@' in e_mail)>0; e_mail LIKE '%@%'} Set2: {e_mail~'\S'; e_mail!~'^[[:space:]]*$'; e_mail!~'^\s*$'} If column e_mail has a constraint from Set1, then it does not need a constraint from Set2. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:39 | MIT License | View |
| 963 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings) | This query identifies superfluous CHECK constraints by detecting logical subsumption. It targets columns where a generic validation ensuring the trimmed string is not empty (e.g., trim(column) <> '') is rendered redundant by a more specific constraint that enforces a minimum length on the trimmed string (e.g., char_length(trim(column)) > 0). Since a string with a positive length is inherently not empty, the generic check adds no functional value and should be removed to simplify the schema. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:43 | MIT License | View |
| 964 | Double checking of the maximum character length | This query identifies superfluous CHECK constraints where a programmatic length check duplicates a declarative, data type-based length limit. For instance, a CHECK constraint like char_length(column) <= 100 on a column already defined as VARCHAR(100) is redundant. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:14 | MIT License | View |
| 965 | Find useless coalesce, concat, or concat_ws calls with only one argument | This query identifies superfluous function calls within routines and views, specifically targeting invocations of coalesce(), concat(), or concat_ws() that are supplied with only a single argument. These functions are variadic and designed to operate on multiple values (e.g., returning the first non-null value or joining strings). When called with a single argument, they function as an identity operation, returning the input unchanged. This pattern indicates either a coding error (missing arguments) or redundant logic that should be removed to simplify the expression. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-30 13:51 | MIT License | View |
| 966 | 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 |
| 967 | Unique constraints made redundant by an exclude constraint | This query identifies superfluous UNIQUE constraints where the constraint is logically subsumed by a more general EXCLUDE constraint on the same table. It targets cases where the set of columns in a UNIQUE or PRIMARY KEY constraint is a subset of (or equal to) the columns in an EXCLUDE constraint, provided the EXCLUDE constraint uses the equality operator (=) for those same columns. In this scenario, the EXCLUDE constraint already enforces uniqueness as part of its more complex logic, rendering the separate UNIQUE constraint redundant. Eliminating this duplication improves schema clarity and removes an unnecessary constraint check. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 09:56 | MIT License | View |
| 968 | Perhaps an unnecessary default value (the empty string or a string that consists of only whitespace) of a base table column/domain | This query identifies table columns and domains that are configured with a semantically void DEFAULT value. It specifically flags defaults that are an empty string ('') or a string consisting solely of whitespace characters (e.g., spaces, newlines). This practice is a design flaw because it automatically populates the database with non-substantive data, which can lead to application-level bugs when code does not explicitly check for such "blank" values in addition to NULL. | Problem detection | INFORMATION_SCHEMA only | 2025-11-12 15:02 | MIT License | View |
| 969 | Base tables that have a surrogate key and do not have any uniqueness constraints | This query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 15:29 | MIT License | View |
| 970 | Too generic names (tables) | This query identifies tables with semantically weak, generic names that violate schema design best practices. It flags tables with name components such as "table", "data", "information", or "list". The principle is that a table name should accurately represent the real-world entity it models. Using generic nouns obscures the schema's meaning, reduces readability, and forces developers to inspect the table's contents to understand its purpose. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 14:10 | MIT License | View |
| 971 | Perhaps an unsuitable use of CHAR(n) type in base tables (based on names) | This query identifies the semantic misuse of the CHAR(n) data type for non-foreign key columns where n > 1. It operates on a heuristic, flagging columns whose names suggest they store variable-length data (e.g., "name", "comment", "description", "email") rather than genuinely fixed-length data like standardized codes or hash values. Because CHAR(n) is a fixed-width, space-padded type, its use for variable-length strings is inefficient in terms of storage and can introduce application-level logic errors, making VARCHAR(n) the appropriate alternative. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 15:08 | MIT License | View |
| 972 | Triggers with SELECT (i.e., probably check data based on another table) | This query identifies trigger functions intended for constraint enforcement that are susceptible to concurrency anomalies due to PostgreSQL's Multi-Version Concurrency Control (MVCC) model. Since read operations (SELECT) do not block write operations, a trigger that validates cross-row constraints without acquiring explicit locks (e.g., LOCK TABLE or SELECT ... FOR UPDATE) involves a race condition. The query detects triggers that query auxiliary data. | General | INFORMATION_SCHEMA+system catalog base tables | 2026-01-19 15:19 | MIT License | View |
| 973 | Useless trivial trigger functions | This query identifies trigger functions that are functionally trivial, specifically those whose sole action is to execute RETURN NEW. In a BEFORE trigger context, this operation simply allows the data modification to proceed unchanged. If the function contains no other logic (e.g., validation, modification of NEW, or side effects), it performs no useful work and incurs unnecessary execution overhead. Such triggers are likely incomplete placeholders or obsolete code that should be removed. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-05 11:50 | MIT License | View |
| 974 | 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 |
| 975 | 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 |
| 976 | 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 |
| 977 | Functions that have transactional control | This query identifies user-defined functions that contain restricted Transaction Control Language (TCL) statements, such as BEGIN, COMMIT, ROLLBACK, or SAVEPOINT. In PostgreSQL, functions execute within the context of the calling query's transaction and are strictly prohibited from managing transaction boundaries. Attempting to execute these commands within a function body results in a runtime error. To implement transactional logic (such as committing data in batches), the code must be refactored into a PROCEDURE, which supports transaction management. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-14 12:25 | MIT License | View |
| 978 | Useless trivial non-trigger functions | This query identifies user-defined routines (excluding triggers) that are functionally trivial. It flags routines whose body consists solely of returning a static value: either an input argument (identity function), a constant literal, or NULL. Such routines typically perform no computation, data manipulation, or side effects. They are likely placeholders, deprecated logic, or artifacts of incomplete refactoring, and should be reviewed for removal or implementation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-05 11:44 | MIT License | View |
| 979 | Updating or deleting data in a routine without restricting rows | This query identifies user-defined routines that contain unbounded Data Modification Language (DML) statements. Specifically, it flags routines containing UPDATE or DELETE operations that lack a qualifying WHERE clause. Such statements result in full-table modifications, affecting every row in the target relation. While valid in specific maintenance contexts, this pattern typically represents a critical logic error in transactional code, posing a severe risk of unintended massive data loss or corruption. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-20 18:27 | MIT License | View |
| 980 | Explicit locking | This query identifies user-defined routines that employ explicit locking mechanisms to supplement PostgreSQL's default Multi-Version Concurrency Control (MVCC). It detects the presence of table-level locking (LOCK TABLE) or explicit row-level locking clauses (e.g., SELECT ... FOR UPDATE, FOR SHARE). While MVCC generally provides sufficient isolation for concurrent transactions, explicit locking is necessary in specific race-condition scenarios. This inventory assists in auditing concurrency control strategies and detecting potential sources of deadlocks or serialization bottlenecks. | General | INFORMATION_SCHEMA+system catalog base tables | 2025-12-10 13:13 | MIT License | View |