Filter Queries
- All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
- Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets, it shouldn't be a problem in case of the relatively small amount of data in the system catalog of a database.
#901. Timezone-aware logic on timezone-unaware columns
system catalog base tables onlyThis 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.
#902. Too few rounds in case of calculating the hash
INFORMATION_SCHEMA+system catalog base tablesFind user-defined routines where gen_salt function is used with the number of rounds that is smaller than 12. The number should be adjusted based on the hardware where the system resides. Password hashing should take at least 250 ms.
#903. Too generic names (candidate key columns)
system catalog base tables onlyFind candidate key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix. These are too generic names.
#904. Too generic names (columns)
INFORMATION_SCHEMA+system catalog base tablesThis query audits the schema for semantically weak column identifiers. It identifies columns named with generic nouns (e.g., data, info, value) or context-free technical terms (e.g., id, type, code, date, fk, pk). Such names violate the principle of self-documenting schema design, as they fail to describe the specific domain attribute being stored. To prevent ambiguity—especially in complex joins—column names should carry sufficient semantic context (e.g., product_type instead of type, creation_date instead of date).
#905. Too generic names (columns) (aggregate view)
INFORMATION_SCHEMA+system catalog base tablesThis query generates a frequency distribution of semantically weak column identifiers within the schema. It aggregates the occurrences of generic names (e.g., id, type, data) to quantify their prevalence across the database. This statistical view serves as a prioritization tool for refactoring, highlighting the most ubiquitous violations of naming conventions that contribute to schema ambiguity.
#906. Too generic names (columns) (there is a column with a more specific name in the table)
INFORMATION_SCHEMA+system catalog base tablesThis query identifies columns that appear overly generic because the same table also contains a more specialized sibling column whose name includes the original column's name as a prefix or suffix. For example, if a table defines both parent and root_parent, the standalone name parent becomes ambiguous and should likely be clarified (e.g., renamed to immediate_parent or direct_parent). The query excludes columns whose names indicate they are codes or identifiers (such as _id or _code), as those commonly follow distinct naming patterns.
#907. Too generic names (domain constraints)
INFORMATION_SCHEMA onlyFind domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1").
#908. Too generic names (foreign key columns)
system catalog base tables onlyFind foreign key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix that describes the nature of relationship with the referenced table or at least referes to the referenced table. These are too generic names.
#909. Too generic names (many-to-many relationship types that do not have additional attributes)
INFORMATION_SCHEMA+system catalog base tablesThis query identifies junction tables with semantically redundant names, specifically targeting those whose names contain the word "join". The structure of a many-to-many table (typically two foreign keys forming a composite primary key) is inherently declarative of its role as a join mechanism. Including the word "join" in the name is superfluous and violates the principle of naming based on the entity or relationship being modeled, not the implementation detail.
#910. Too generic names (parameters)
INFORMATION_SCHEMA+system catalog base tablesFind the names of parameters that are too generic. The routines work with data/information. Thus, it is not a good style to use generic words like data, information, parameter, etc. In the names of parameters. Moreover, avoid too generic parameter names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk, param.
#911. Too generic names (parameters) (there is a parameter with a more specific name in the routine)
INFORMATION_SCHEMA+system catalog base tablesFind parameter names in case of which the same routine has another parameter with the same mode but with more specific name, i.e., the name contains the parameter name in the end or in the beginning.
#912. Too generic names regarding persons and their names (columns of derived tables)
INFORMATION_SCHEMA+system catalog base tablesThis query identifies columns in views (derived tables) whose names suggest they store a person's name but lack context about the person's role. In a view, a column containing a person's name should indicate their specific role regarding the entity. For example, a column simply named surname in an active_product view is ambiguous; a more descriptive name, such as registrar_surname, is preferred. However, if the primary purpose of the view is to present personal data, generic names like surname are perfectly acceptable.
#913. Too generic names (routines)
INFORMATION_SCHEMA+system catalog base tables"Avoid using the same word for two purposes. Using the same term for two different ideas is essentially a pun" (Robert C. Martin, Clean Code) Do not use the Estonian words like 'lisa', 'muuda', 'kustuta' or the corresponding English words 'add', 'delete', 'update' as the names of routines because it would make the code much less understandable. What is the task of the routine f_add? Register people? Register orders? What? Do not refer to the generic concepts like data and information because all routines work with these.
#914. Too generic names (table constraints)
system catalog base tables onlyFind table constraints (constraints that are associated directly with the table) that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name.
#915. Too generic names (tables)
INFORMATION_SCHEMA+system catalog base tablesThis 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.
#916. Too generic names (unique index columns)
system catalog base tables onlyFind unique index (not associated with a constraint) columns with the names like id, identifikaator, code, kood, number, etc. The names could have underscores as the prefix or suffix. These are too generic names.
#917. Too generic names (unique indexes)
system catalog base tables onlyFind unique indexes that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name.
#918. Too generic names (user-defined types, domains)
system catalog base tables onlyFind names of user-defined types and domains that contain too generic (noise) words.
#919. Too many slashes in regular expressions
INFORMATION_SCHEMA+system catalog base tablesFind patterns of regular expressions where more than \ is written instead of \, e.g., \\s is used instead of \s to refer to a character class.
#920. Too short domain constraint names
INFORMATION_SCHEMA onlyFind names of domain constraints that are shorter than the length of the name of the domain + two characters.
| # | Name(activate to sort ascending) | Goal(activate to sort ascending) | Type(sorted ascending, activate to sort descending) | Data source(activate to sort ascending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 901 | 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 | MIT (opens in new tab) | View | |
| 902 | Too few rounds in case of calculating the hash | Find user-defined routines where gen_salt function is used with the number of rounds that is smaller than 12. The number should be adjusted based on the hardware where the system resides. Password hashing should take at least 250 ms. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 903 | Too generic names (candidate key columns) | Find candidate key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix. These are too generic names. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 904 | Too generic names (columns) | This query audits the schema for semantically weak column identifiers. It identifies columns named with generic nouns (e.g., data, info, value) or context-free technical terms (e.g., id, type, code, date, fk, pk). Such names violate the principle of self-documenting schema design, as they fail to describe the specific domain attribute being stored. To prevent ambiguity—especially in complex joins—column names should carry sufficient semantic context (e.g., product_type instead of type, creation_date instead of date). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 905 | Too generic names (columns) (aggregate view) | This query generates a frequency distribution of semantically weak column identifiers within the schema. It aggregates the occurrences of generic names (e.g., id, type, data) to quantify their prevalence across the database. This statistical view serves as a prioritization tool for refactoring, highlighting the most ubiquitous violations of naming conventions that contribute to schema ambiguity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 906 | Too generic names (columns) (there is a column with a more specific name in the table) | This query identifies columns that appear overly generic because the same table also contains a more specialized sibling column whose name includes the original column's name as a prefix or suffix. For example, if a table defines both parent and root_parent, the standalone name parent becomes ambiguous and should likely be clarified (e.g., renamed to immediate_parent or direct_parent). The query excludes columns whose names indicate they are codes or identifiers (such as _id or _code), as those commonly follow distinct naming patterns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 907 | Too generic names (domain constraints) | Find domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1"). | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 908 | Too generic names (foreign key columns) | Find foreign key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix that describes the nature of relationship with the referenced table or at least referes to the referenced table. These are too generic names. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 909 | Too generic names (many-to-many relationship types that do not have additional attributes) | This query identifies junction tables with semantically redundant names, specifically targeting those whose names contain the word "join". The structure of a many-to-many table (typically two foreign keys forming a composite primary key) is inherently declarative of its role as a join mechanism. Including the word "join" in the name is superfluous and violates the principle of naming based on the entity or relationship being modeled, not the implementation detail. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 910 | Too generic names (parameters) | Find the names of parameters that are too generic. The routines work with data/information. Thus, it is not a good style to use generic words like data, information, parameter, etc. In the names of parameters. Moreover, avoid too generic parameter names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk, param. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 911 | Too generic names (parameters) (there is a parameter with a more specific name in the routine) | Find parameter names in case of which the same routine has another parameter with the same mode but with more specific name, i.e., the name contains the parameter name in the end or in the beginning. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 912 | Too generic names regarding persons and their names (columns of derived tables) | This query identifies columns in views (derived tables) whose names suggest they store a person's name but lack context about the person's role. In a view, a column containing a person's name should indicate their specific role regarding the entity. For example, a column simply named surname in an active_product view is ambiguous; a more descriptive name, such as registrar_surname, is preferred. However, if the primary purpose of the view is to present personal data, generic names like surname are perfectly acceptable. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 913 | Too generic names (routines) | "Avoid using the same word for two purposes. Using the same term for two different ideas is essentially a pun" (Robert C. Martin, Clean Code) Do not use the Estonian words like 'lisa', 'muuda', 'kustuta' or the corresponding English words 'add', 'delete', 'update' as the names of routines because it would make the code much less understandable. What is the task of the routine f_add? Register people? Register orders? What? Do not refer to the generic concepts like data and information because all routines work with these. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 914 | Too generic names (table constraints) | Find table constraints (constraints that are associated directly with the table) that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 915 | 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 | MIT (opens in new tab) | View | |
| 916 | Too generic names (unique index columns) | Find unique index (not associated with a constraint) columns with the names like id, identifikaator, code, kood, number, etc. The names could have underscores as the prefix or suffix. These are too generic names. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 917 | Too generic names (unique indexes) | Find unique indexes that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 918 | Too generic names (user-defined types, domains) | Find names of user-defined types and domains that contain too generic (noise) words. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 919 | Too many slashes in regular expressions | Find patterns of regular expressions where more than \ is written instead of \, e.g., \\s is used instead of \s to refer to a character class. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 920 | Too short domain constraint names | Find names of domain constraints that are shorter than the length of the name of the domain + two characters. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View |