Seq nr | Name | Goal | Type | Data source | Last update▼ | License | ... |
---|---|---|---|---|---|---|---|
181 | Perhaps checking of file extension is incorrect | Find check constraints of tables that use a regular expression to check as to whether a registered string ends with an appropriate file extension. However, the expression does not put the dot sign into the square brackets nor does have the escape character \before it, i.e., it is interpreted as a single character not as the dot sign in the expression. In regular expressions the dot (.) matches any single character except the newline character. | Problem detection | INFORMATION_SCHEMA only | 2023-11-08 15:29 | MIT License | |
182 | Column names that make joining tables more difficult | Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. | Problem detection | system catalog base tables only | 2023-11-08 13:56 | MIT License | |
183 | Column names that make joining tables more difficult (quite similar names) | Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is between two and four characters. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. | Problem detection | system catalog base tables only | 2023-11-08 13:56 | MIT License | |
184 | Column names that make joining tables more difficult (very similar names) | Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is one character. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. | Problem detection | system catalog base tables only | 2023-11-08 13:56 | MIT License | |
185 | Perhaps the type of a base table column should be an integer type (based on column names) | Find columns of base tables where the name of the column has prefix or suffix "id" or has the name "id" but the column does not have an integer type or uuid type. A convention is to use the phrase "id" in the names of surrogate key columns. | Problem detection | INFORMATION_SCHEMA only | 2023-11-08 13:31 | MIT License | |
186 | Textual columns that have a secondary index but the operator class for the column does not support pattern matching | Find indexed textual columns where the indexing does not consider the possibility of pattern-based search. Such columns do not have an index where the used operator class makes the index suitable for use by queries involving pattern matching expressions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-07 11:52 | MIT License | |
187 | Tsvector values are not automatically calculated | Find base table columns with tsvector type in case of which it is not a generated column nor does the table has an associated trigger to calculate the tsvector value automatically. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-07 11:45 | MIT License | |
188 | Inconsistent means to calculate tsvector values | Find as to whether in the database there are multiple ways to calculate tsvector values, i.e., by using a generated column and by not using a generated column. | Problem detection | INFORMATION_SCHEMA only | 2023-11-07 11:35 | MIT License | |
189 | Multiple triggers that update tsvector values | Find base tables that have multiple triggers to update tsvector values. | Problem detection | system catalog base tables only | 2023-11-07 10:14 | MIT License | |
190 | Triggers that are used to calculate tsvector values react to a wrong set of events | Find triggers on base tables that are used to calculate tsvector values that react to a wrong set of events, i.e., react to the DELETE event or do not react to the INSERT and UPDATE events. | Problem detection | system catalog base tables only | 2023-11-07 10:14 | MIT License | |
191 | Names of database objects that are fully uppercase | Full uppercase means screaming and it makes comprehending the names more difficult. Find the names (identifiers) of user-defined database objects that are fully uppercase. Because PostgreSQL stores regular identifiers lowercase in the system catalog it also means that these are delimited identifiers, i.e., these are case sensitive. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-07 09:28 | MIT License | |
192 | 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 | 2023-11-07 09:22 | MIT License | |
193 | Full text search columns with other type of index than 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 but have another type of index (e.g., b-tree). Gin and Gist are the preferred index types for text search. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-07 09:22 | MIT License | |
194 | Exclude constraint to prevent overlapping time periods | Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-11-07 00:44 | MIT License | |
195 | Base tables and materialized views without any index | Find base tables and materialized views that do not have any index. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-05 19:39 | MIT License | |
196 | Stating the obvious (column names) | Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer. | Problem detection | INFORMATION_SCHEMA only | 2023-11-04 15:19 | MIT License | |
197 | Inconsistent time zone and precision usage in case of registering times | Find as to whether different data types (with and without timezone) and precisions are used in case of registering times in different columns. | Problem detection | INFORMATION_SCHEMA only | 2023-11-04 12:30 | MIT License | |
198 | Inconsistent time zone and precision usage in case of registering timestamps | Find as to whether different data types (with and without timezone) and precisions are used in case of registering timestamps in different columns. | Problem detection | INFORMATION_SCHEMA only | 2023-11-04 12:28 | MIT License | |
199 | Subqueries of derived tables with LIMIT/FETCH/DISTINCT ON without ORDER BY | Find subqueries of derived tables (views, materialized views) with the LIMIT/FETCH clause or with DISTINCT ON construct but without the ORDER BY clause. These constructs require sorting to produce a meaningful result. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-03 16:56 | MIT License | |
200 | Precision of a timestamp or a time column is too big | Find columns with a timestamp or a time type where the precision (the permitted maximum number of fractional sections) is bigger than the precision in the default value of the column. | Problem detection | INFORMATION_SCHEMA only | 2023-11-02 17:29 | MIT License |