Seq nr | Name | Goal | Type | Data source▲ | Last update | License | ... |
---|---|---|---|---|---|---|---|
301 | Default should be declared at the level of domain not at the level of base table columns | Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
302 | Definition of a non-minimal superkey instead of a candidate key (based on check constraints) | Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has a check constraint that permits only one value in the column. The candidate key should involve only columns without such constraint. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-04-30 20:25 | MIT License | |
303 | Definition of a non-minimal superkey instead of a candidate key (based on enumeration types) | Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has an enumeration type that permits only one value in the column. The candidate key should involve only columns without such type. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
304 | Definition of a non-minimal superkey instead of a candidate key (based on sequence generators) | Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary keys and unique constraints where a proper subset of the key is a column with an associated sequence generator. Most certainly such subset contains unique values. Thus, this subset should be the key, not its superset. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 20:56 | MIT License | |
305 | Delimited identifiers | Delimited identifiers (quoted identifiers) are case sensitive. Identifiers of database objects should be case insensitive in order to simplify their management. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-03-18 16:38 | MIT License | |
306 | 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 | |
307 | 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 | |
308 | 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 | |
309 | 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 | |
310 | 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 | |
311 | 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 | |
312 | 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 | |
313 | 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 | |
314 | 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 | |
315 | 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 | |
316 | 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 | |
317 | 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 | |
318 | 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 | |
319 | Domain based on another domain | Find domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
320 | Domain candidates | Find column descriptions that are candidates for describing a domain. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License |