Seq nr | Name▲ | Goal | Type | Data source | Last update | License | ... |
---|---|---|---|---|---|---|---|
221 | Derived table on top of another derived table | Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
222 | Derived table presents the same data in the same way as a single base table | Find derived tables (views and materialized views) that present data from one base table without adding columns, renaming columns, changing the order of columns, removing columns, or restricting rows. Perhaps something is missing from the subquery of the derived table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-25 12:15 | MIT License | |
223 | Derived tables that have a column with the xid type | Find the derived tables (views and materialized views) that have a column with the xid type, i.e., these use the data from the hidden xmin column of a base table. If one uses optimistic approach for dealing with the concurrent data modifications, then xmin values should be presented by views and used in routines that modify or delete rows. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
224 | Derived tables that present data in json or xml format | Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
225 | Derived tables with multiple DISTINCT's | Find derived tables (views and materialized views) that contain more than one DISTINCT invocation. Make sure that the query is correctly written, including that it does not have unwanted formation of a Cartesian product. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-10 13:25 | MIT License | |
226 | Derived tables with ranking | Find views and materialized views that use rank and dense_rank window functions. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
227 | Derived tables with sorting | Find derived tables where the rows are ordered, i.e., there is ORDER BY clause at the end of the view. Different users may want to see the rows in different order. Thus, the DBMS may have to do extra and unnecessary work by firstly sorting based on one set of rows and after that based on other set of rows. Use sorting if you know that all the users want the rows in the same order and cannot/will not change the order in the application. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-22 21:34 | MIT License | |
228 | Derived tables with string_agg | Find views and materialized views that use string_agg aggregate function. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
229 | Derived table uses a function to get data from another table | Find views that use a function to get data from another table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-24 14:36 | MIT License | |
230 | Deterministic (immutable) functions that do not have input parameters | Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-09 19:53 | MIT License | |
231 | Deterministic (immutable) functions that do not return a value | Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-20 19:33 | MIT License | |
232 | Different character maximum lengths that are used to define textual base table columns | Find the number of different character maximum lengths that are used to define textual base table columns as well as list all the different lengths. Show also the total number of columns with char/varchar type. Maximum character length constrains values in a column. Thus, in case there is a small number of used lengths, it raises a question as to whether the lengths have been optimally selected. | Sofware measure | INFORMATION_SCHEMA only | 2021-03-26 11:24 | MIT License | |
233 | Different data types that are used to define base table columns | Find the number of different data types that are used to define base table columns as well as list all the different types. Data type constrains values in a column. Thus, in case there is a small number of used types, it raises a question as to whether the types have been optimally selected. | Sofware measure | INFORMATION_SCHEMA only | 2020-11-27 10:40 | MIT License | |
234 | Different foreign key column names in case of referencing the same candidate key | Find the cases when the names of columns in different foreign keys that reference to the same candidate key are different. If different names reflect different roles, then it is legitimate. However, there could also be accidental differences that makes it more difficult to use the database. | Problem detection | system catalog base tables only | 2021-03-12 11:21 | MIT License | |
235 | Different non-surrogate key default values | Find the different default values that implement something other than a surrogate key. | General | INFORMATION_SCHEMA only | 2021-03-04 10:31 | MIT License | |
236 | Different prefixes of a candidate key column and a referencing foreign key column | The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different prefixes. Thus, for instance, one cannot use USING syntax for joining the tables. | Problem detection | system catalog base tables only | 2021-02-25 17:29 | MIT License | |
237 | Different search paths of SECURITY DEFINER functions | Find the different search paths used in case of SECURITY DEFINER functions and the number of their occurrences. Make sure that these have been specified correctly and consistently and that they do not refer to any non-existent schemas. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-16 10:59 | MIT License | |
238 | Different suffixes of a candidate key column and a referencing foreign key column | The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different suffixes. Thus, for instance, one cannot use USING syntax for joining the tables. | Problem detection | system catalog base tables only | 2021-02-25 17:29 | MIT License | |
239 | Different tasks of rules | Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task. | General | system catalog base tables only | 2024-01-14 16:10 | MIT License | |
240 | Different tasks of triggers | Find different tasks that are solved by using triggers, i.e., different triggers on the same table or different tables that do the same thing are considered to solve one task. | General | INFORMATION_SCHEMA+system catalog base tables | 2024-01-04 00:53 | MIT License |