| # | Name | Goal | Type ▲ | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 281 | CHAR columns have a default value that length is shorter from the character maximum length of the column | Choose a suitable data type, field size, and default value. If the default value is shorter from the character maximum length, then spaces will be added to the end of the registered value. | Problem detection | INFORMATION_SCHEMA only | 2025-11-07 10:11 | MIT License | View |
| 282 | CHAR or VARCHAR columns have a default value that length is longer from the character maximum length of the column | Find table columns with CHAR or VARCHAR type that have a default value that length is longer from the character maximum length of the column. Choose a suitable data type, field size, and default value. If the value is longer, then it is impossible to register it in a field, i.e., it makes registration of data impossible (except if the excessive characters are spaces). | Problem detection | INFORMATION_SCHEMA only | 2025-11-07 10:11 | MIT License | View |
| 283 | CHECK constraint cardinality is zero | Write correct constraints. Usually the constraint expression should refer to at least one column. A domain constraint expression should refer to the stub VALUE. For instance, the constraint CHECK(1=0) that is associated with a table T would prevent adding any rows to T. The value of the Boolean expression of this constraint is always FALSE. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 284 | CHECK constraints are inconsistent with DEFAULT values | This query identifies a logical contradiction between data validation rules and default value definitions. It flags CHECK constraints involving two columns where both columns share the same DEFAULT value, yet the constraint enforces a strict inequality (e.g., col1 < col2) or non-equality (col1 <> col2). This configuration is logically flawed because attempting to insert a row using the default values for both columns will result in an immediate constraint violation (as X < X evaluates to false). This renders the default values mutually exclusive and unusable in practice. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-14 13:28 | MIT License | View |
| 285 | 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 |
| 286 | 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 |
| 287 | CHECK constraints that perhaps do not consider 'infinity' and '-infinity' special values | Find check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 288 | CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values | Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 289 | CHECK constraints with IS NULL | Find CHECK constraints to one column (associated with a base table directly or through domain) that check that the value is missing (IS NULL). Write as simple constraint definitions as possible. By default columns are optional, i.e., they permit NULLs. NULL in a column means that checking of a CHECK constraint on the column results with UNKNOWN. CHECK constraints permit rows in case of which checking results with TRUE or UNKNOWN. In case of a CHECK constraint there is no need to check separately that a value in the column could be missing, i.e., be NULL. Thus, for instance, instead of writing CHECK (price>0 OR price IS NULL) write CHECK (price>0). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 290 | CHECK constraint with pattern matching on non-textual columns | This query identifies a semantic mismatch between data types and constraint logic. It targets base and foreign table columns that are defined with non-textual data types (e.g., INTEGER, DATE, BOOLEAN) but are subject to single-column CHECK constraints utilizing string pattern matching operators (LIKE, SIMILAR TO, or regular expressions). This practice forces implicit casting to text, which is computationally inefficient and indicates a design flaw. It suggests that either the column should utilize a textual data type, or the constraint should be rewritten using operators appropriate for the actual data type (e.g., numeric ranges instead of regex). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-05 19:13 | MIT License | View |
| 291 | CHECKs are associated with a column instead of the domain of the column | Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 292 | Column names that make joining more difficult (foreign key column name contains the table name) | This query identifies foreign key columns where the identifier diverges from the referenced candidate key solely due to the redundant inclusion of the referencing table's name (as a prefix or suffix). Such naming redundancy precludes the use of the simplified SQL USING syntax in join operations, forcing the use of the more verbose ON clause. Harmonizing these column names (i.e., making the foreign key name identical to the referenced column name) enables more concise query formulation and improves schema readability. | Problem detection | system catalog base tables only | 2025-12-14 11:56 | MIT License | View |
| 293 | Column names that make joining more difficult (foreign key column name equals the referenced table name) | This query identifies foreign key columns where the identifier is identical to the name of the referenced table. This naming pattern typically results in a mismatch between the foreign key column and the referenced primary key column (e.g., a column named department referencing a table department with a primary key department_id). This mismatch precludes the use of the simplified ANSI SQL USING clause in join operations, necessitating the use of the more verbose ON clause. Harmonizing the column name to match the referenced key enables more concise query formulation. The Example: A table Employees has a column named Department that links to the Department table (where the ID is department_id). The Problem: Because the column is named Department and not department_id, you cannot use the shortcut syntax: JOIN Department USING (department_id). You are forced to write: JOIN Department ON Employees.Department = Department.department_id. |
Problem detection | system catalog base tables only | 2025-12-15 11:07 | MIT License | View |
| 294 | Column names that make joining tables more difficult | This query identifies foreign key columns where the identifier differs from the referenced candidate key identifier. It explicitly excludes self-referencing constraints (recursive relationships), where name divergence is structurally mandatory. The primary objective is to identify opportunities to harmonize column names across the schema. Synchronizing the foreign key name with the referenced column name facilitates the use of the ANSI SQL USING clause in join operations (e.g., JOIN t1 USING (client_id)), which is significantly more concise than the explicit ON predicate required when names differ. | Problem detection | system catalog base tables only | 2025-12-14 11:31 | MIT License | View |
| 295 | Column names that make joining tables more difficult (quite similar names) | This query identifies foreign key columns where the identifier deviates slightly from the referenced candidate key, specifically exhibiting a textual difference (Levenshtein distance) of two to four characters. This range typically captures minor prefixes (e.g., fk_) or suffixes that prevent the use of the concise SQL USING syntax in join operations. The query explicitly excludes self-referencing constraints (recursive relationships), where distinct column names are structurally mandatory. Aligning these names allows for cleaner, more readable query formulation. | Problem detection | system catalog base tables only | 2025-12-14 11:29 | MIT License | View |
| 296 | Column names that make joining tables more difficult (table names) | This query identifies foreign key relationships where the identifier of the referenced column diverges from the foreign key column solely due to the inclusion of the table name. It specifically targets cases where the referenced column name is formed by concatenating the target table name with the target column name (e.g., referenced_col = table_name || '_' || fk_col). This naming redundancy prevents the use of the simplified SQL USING syntax for join operations, necessitating verbose ON clauses. Harmonizing these identifiers by standardizing the naming convention improves schema readability and query conciseness. | Problem detection | system catalog base tables only | 2025-12-14 11:53 | MIT License | View |
| 297 | Column names that make joining tables more difficult (very similar names) | This query identifies foreign key columns where the identifier exhibits a minimal textual deviation (exactly one character) from the referenced candidate key. This specific proximity often indicates a typographical error or a singular/plural inconsistency (e.g., user_id vs users_id). The query explicitly excludes self-referencing constraints, where name divergence is structurally mandatory. Harmonizing these names enables the use of the simplified SQL USING syntax for joins, replacing verbose ON clauses and improving query readability. | Problem detection | system catalog base tables only | 2025-12-14 11:30 | MIT License | View |
| 298 | Columns of base tables that hold truth values but do not have a default value (Boolean columns) | Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type. | Problem detection | INFORMATION_SCHEMA only | 2025-11-07 10:11 | MIT License | View |
| 299 | Columns of base tables that hold truth values but do not have a default value (non-Boolean columns) | Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 300 | Columns of base tables that hold truth values but do not restrict the permitted values (non-Boolean columns) | Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a have a check constraint. The constraint should restrict the permitted values with values that represent truth values TRUE and FALSE. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |