| 381 |
Find columns that require new lookup tables (based on names and field sizes) |
This query detects data attributes that function as implicit classifiers but lack a corresponding reference entity. It targets textual columns that are not currently constrained (no PK, UK, or FK) but exhibit characteristics of coded data: they either have a very short length (≤ 3 characters) or possess identifiers typical of classifiers (e.g., 'status', 'type'). To reduce false positives, it excludes obvious free-text fields (names, comments) and verifies that no table with a similar name currently exists. This suggests the need to extract these attributes into a new dedicated reference table to enforce domain integrity. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-30 09:14 |
MIT License |
View |
| 382 |
Find inconsistency in double underscore/space usage |
This query audits the database schema for inconsistency regarding the use of consecutive separators (double underscores or spaces) within identifiers. It groups objects by type (e.g., CHECK constraints, indexes) and identifies categories where a mixed naming convention exists—specifically, where some identifiers utilize consecutive separators (e.g., idx__name) while others of the same type do not (e.g., idx_name). This variation suggests a lack of enforced coding standards, leading to unpredictability in the schema. The query facilitates a review to establish and enforce a single, uniform naming convention. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-20 13:08 |
MIT License |
View |
| 383 |
Find mixed usage of \w and [[:alnum:]] regex syntax |
This query audits regular expressions within the database to detect inconsistent syntax for matching alphanumeric characters. It checks for the concurrent use of both the Perl-style shorthand \w and the POSIX character class [[:alnum:]]. While these are often functionally similar, their exact behavior can differ based on locale settings (e.g., \w may include underscores while [[:alnum:]] does not). Using both styles within the same codebase indicates a lack of a clear standard, which can lead to maintainability issues and subtle, locale-dependent bugs. Standardizing on a single, well-understood syntax is recommended for clarity and predictability. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 11:11 |
MIT License |
View |
| 384 |
Find || operations missing coalesce() protection |
This query identifies potential null-propagation defects in user-defined routines and views. It targets subqueries utilizing the standard concatenation operator (||) where operands are not protected by a coalesce() function. In PostgreSQL, the operation string || NULL yields NULL, causing the entire result to vanish if any component is missing. This behavior is often unintentional. The query flags these risky patterns, suggesting remediation via explicit null handling or the adoption of null-safe alternatives like concat(), concat_ws(), or format(). |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-27 11:50 |
MIT License |
View |
| 385 |
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 |
| 386 |
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 |
| 387 |
Flag parameters |
A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 388 |
Foreign key column has a default value that is not present in the parent table |
Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 389 |
Foreign key columns that are associated with a sequence generator |
Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 390 |
Foreign key columns that do not have an integer or varchar type |
Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 391 |
Foreign key columns that have no index |
Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 392 |
Foreign key references a non-key (has optional columns) |
Find foreign key constraints that referenced column is optional. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 393 |
Foreign servers without user mappings |
Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource." |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 394 |
Frequency of column name lengths based on the table type |
Find in case of base tables, materialized views, and views the number of columns based on the length of the column name. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 395 |
Frequency of lengths of the names of database objects |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find how many names (identifiers) of database objects there are with different lengths. The values could be used to compare different databases. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 396 |
Frequency of name components of database objects |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find, what are the most popular components of the names (identifiers) of user-defined database objects, assuming, that the separator of the components is "_". |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 397 |
Frequency of table name lengths based on the table type |
This query provides a statistical analysis of identifier length across the schema. It calculates a frequency distribution by grouping base tables, views, and materialized views based on the character length of their names. The result is a count of how many objects exist for each distinct name length, which can be used to audit naming conventions or identify outliers. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 12:56 |
MIT License |
View |
| 398 |
Frequent names of database objects |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find the names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average. Also make sure that there is no duplication in play. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 399 |
Frequent names of database objects by object type |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find the names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average in case of the particular type of database objects. Also make sure that there is no duplication in play. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 400 |
Full text search columns that have no gin or gist index |
Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index. These are the preferred index types for text search. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |