| 1 |
All derived tables that use joining tables |
Find all derived tables that join two or more tables. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2024-10-05 18:34 |
MIT License |
View |
| 2 |
All updatable views |
Find all views through which it is possible to modify data in base tables. Is_insertable_into and is_updatable show as to whether the view is naturally updatable in PostgreSQL or has associated rules that make sure that INSERT/UPDATE/DELETE operations against the view will not cause an error. Please note that the rule could be DO INSTEAD NOTHING rule, i.e., data modification through the view does not cause an error but the data is not actually modified. If the updatability is achieved due to rules, then is_updatable=YES if the view has both UPDATE and DELETE rule that make sure that UPDATE and DELETE operations do not cause an error. |
General |
INFORMATION_SCHEMA only |
2024-01-14 17:09 |
MIT License |
View |
| 3 |
AND takes precedence over OR |
Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2020-11-06 14:51 |
MIT License |
View |
| 4 |
A non-parameterized table function instead of a view |
Find table functions that do not have any parameters. Prefer simpler and more portable solutions. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-03 21:16 |
MIT License |
View |
| 5 |
A predefine character class has been incorrectly specified |
Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:]. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2023-11-23 12:09 |
MIT License |
View |
| 6 |
A table has the same name as a routine |
Find table names that are the same as some routine name. Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 7 |
Case insensitive search |
Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern). |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:55 |
MIT License |
View |
| 8 |
Coalesce/Concat need at least two arguments |
Find user-defined routines and derived tables (views/materialized views) that have a subquery that contain an invocation of Coalesce or Concat function with just one argument. You should use the Coalesce and Concat functions correctly by invoking these with at least two arguments. If one invokes these functions with one argument, then the functions will return the input value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-12-08 11:49 |
MIT License |
View |
| 9 |
Columns of derived tables that name has been given by the system |
Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. |
Problem detection |
system catalog base tables only |
2023-12-21 12:15 |
MIT License |
View |
| 10 |
Columns of derived tables that name has been given by the system (2) |
Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. |
Problem detection |
system catalog base tables only |
2023-12-21 12:17 |
MIT License |
View |
| 11 |
Comments of derived tables |
Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information. |
General |
system catalog base tables only |
2023-01-19 12:14 |
MIT License |
View |
| 12 |
Coverage by derived tables |
Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2023-11-11 09:31 |
MIT License |
View |
| 13 |
Database objects of the same type and case insensitive name in the same container |
Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2024-12-21 17:13 |
MIT License |
View |
| 14 |
Database object that belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ |
Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 15 |
Derived table names have prefix or suffix |
Find the names of views and materialized views that have prefix or suffix. Follow the same naming style as in case of base tables (derived tables are also tables). Thus, if base tables do not have prefixes or suffixes, then derived tables shouldn't have these as well. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 16 |
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 |
View |
| 17 |
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 |
View |
| 18 |
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 |
View |
| 19 |
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 |
View |
| 20 |
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 |
View |