| 121 |
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 |
| 122 |
Superfluous IS NULL checks in constraints |
This query identifies CHECK constraints that contain redundant logic for handling NULLs, a pattern often arising from a misunderstanding of SQL's three-valued logic (TRUE, FALSE, UNKNOWN). A CHECK constraint's condition only fails on FALSE, implicitly permitting NULLs by evaluating comparisons to UNKNOWN. Consequently, an explicit OR column IS NULL clause is tautological and adds no functional value, as demonstrated by the equivalence of CHECK (price IS NULL OR price > 0) and CHECK (price > 0). Removing this superfluous logic improves constraint clarity and eliminates a marginal but unnecessary computational step. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 10:19 |
MIT License |
View |
| 123 |
Not enforced constraints |
This query identifies constraints (CHECK and FOREIGN KEY) that exist in the system catalog but are not actively enforced against the table data. |
Problem detection |
system catalog base tables only |
2025-11-15 10:10 |
MIT License |
View |
| 124 |
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 |
| 125 |
Usage of the non-standard now() function |
This query identifies all expressions that use the non-standard now() function. In PostgreSQL, now() is a historical, non-standard alias for the SQL-standard current_timestamp. While they are functionally identical within PostgreSQL (both returning the transaction start timestamp as a TIMESTAMPTZ), the use of current_timestamp is strongly preferred for reasons of code portability and adherence to standards. Standardizing on current_timestamp ensures the code is universally understood and easier to maintain or migrate to other database systems. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 10:01 |
MIT License |
View |
| 126 |
Timezone-aware logic on timezone-unaware columns |
This query identifies logically flawed CHECK constraints on columns with timezone-unaware data types (DATE and TIMESTAMP WITHOUT TIME ZONE). It specifically targets constraints that contain timezone-aware logic (e.g., using AT TIME ZONE). This represents a critical semantic mismatch: the data type stores a "naive" or "local" time with no timezone context, while the constraint attempts to evaluate it within a specific timezone. The outcome of such a check is non-deterministic, as it depends on the session's current TimeZone setting, leading to unpredictable and unreliable data validation. This is a design flaw; if timezone-aware logic is required, the column should use the TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) data type. |
Problem detection |
system catalog base tables only |
2025-11-15 09:58 |
MIT License |
View |
| 127 |
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 |
| 128 |
Inconsistent CHECK constraints on columns with the same name |
This query identifies inconsistencies in data validation for columns that share the same name across different tables. It flags cases where a conceptual data element (e.g., 'email', 'postal_code') is subject to a CHECK constraint in some tables but lacks one in others. This violates the principle of uniform data integrity, creates semantic ambiguity, and can allow invalid data to enter the system through the unconstrained columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 09:53 |
MIT License |
View |
| 129 |
Many-to-many tables that perhaps need conceptual renaming |
This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names, often with minor variations (e.g., Courses_Lecturer). This naming convention is a design smell as it describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment. |
Problem detection |
system catalog base tables only |
2025-11-15 09:46 |
MIT License |
View |
| 130 |
Many-to-many tables that need conceptual renaming |
This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names (e.g., Course_Lecturer). This naming style is flagged as a design smell because it merely describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment. |
Problem detection |
system catalog base tables only |
2025-11-15 09:45 |
MIT License |
View |
| 131 |
Perhaps an unsuitable use of CHAR(n) type in base tables (based on check constraints) |
This query identifies a logical redundancy and likely data type misuse by finding CHAR columns that have a CHECK constraint on their value's length. The CHAR(n) data type is fixed-width and space-padded, meaning any non-NULL value will have a character length of exactly n. Therefore, a CHECK constraint on length is either superfluous (if it checks <= n) or will always fail (if it checks < n). This pattern indicates that the developer intended to store a variable-length string with a maximum length, for which the VARCHAR data type is the correct and more efficient choice. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 15:08 |
MIT License |
View |
| 132 |
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 |
| 133 |
Empty schemas |
This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 14:15 |
MIT License |
View |
| 134 |
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 |
| 135 |
Names of columns with the type BOOLEAN |
This query retrieves the names of all columns defined with the BOOLEAN data type to facilitate an audit of naming consistency. The primary objective is to verify adherence to a recommended best practice: boolean column names should be prefixed with a semantic predicate, such as is_ (in English) or on_ (in Estonian). This convention enhances the self-documenting nature of the schema and improves the readability of SQL statements by framing the column's purpose as a true/false question. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 14:08 |
MIT License |
View |
| 136 |
Perhaps the type of a base table column should be BOOLEAN (based on column names) |
This query identifies a semantic mismatch between a column's name and its data type. It flags base table columns that adhere to a predicate-based naming convention (i.e., starting with is_, has_, can_, or on_) but are not defined with the BOOLEAN data type. This is a design flaw as it forces developers to infer and manage truthiness through other types (e.g., INTEGER, CHAR(1)), which undermines schema clarity, requires data type coercion in queries, and can compromise data integrity by permitting non-boolean states. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 14:07 |
MIT License |
View |
| 137 |
Is does not return a boolean |
This query identifies user-defined SQL and PL/pgSQL routines (excluding triggers) that exhibit a semantic inconsistency between their name and return type. It flags non-trigger routines whose names begin with a predicate prefix (e.g., is_, has_, can_, on_) but whose defined return type is not BOOLEAN. Such a mismatch violates the principle of self-documenting code, as the name creates a strong expectation of a TRUE/FALSE return value, which the routine's signature then contradicts. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 14:03 |
MIT License |
View |
| 138 |
Perhaps is not snake_case - Boolean-indicating prefix without underscore |
This query identifies database identifiers (types, domains, columns, parameters) that likely violate the snake_case naming convention. It operates on a specific heuristic, flagging any name that begins with a common predicate prefix (is, has, on) but is not immediately followed by an underscore. This pattern is a strong indicator of camelCase (e.g., isActive) or PascalCase (e.g., IsActive) usage, both of which should be refactored to snake_case (e.g., is_active) to maintain a consistent and readable schema. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 14:01 |
MIT License |
View |
| 139 |
Columns with BOOLEAN type that do have a good name |
This query identifies and lists all BOOLEAN columns that conform to the established predicate-based naming convention. It returns columns whose names begin with one of the approved semantic prefixes: is_, has_, can_, or on_. The output serves as a report of schema components that correctly adhere to best practices for clarity and self-documentation, turning column names into unambiguous true/false questions (e.g., is_agreement instead of agreed). |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 13:58 |
MIT License |
View |
| 140 |
Columns with BOOLEAN type that do not have a good name |
This query audits the naming conventions of BOOLEAN columns, enforcing a predicate-based naming convention that begins with is_, has_, can_, or on_. It specifically discourages the use of simple adjectival or past participle forms as column names. For instance, is_agreement is the preferred form over agreed, and on_kinnitatud is preferred over kinnitatud. This standard ensures the column's name is an unambiguous true/false question, which improves schema self-documentation and the readability of SQL queries. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 13:56 |
MIT License |
View |