| 541 | User-defined routines that implement UPSERT operation | Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 17:02 | MIT License | |
| 542 | Sorting rows based on random values in routines without limiting rows | Find routines that contain a statement that sorts rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:57 | MIT License | |
| 543 | Sorting rows based on random values in routines | Find routines that contain a statement that sorts rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:56 | MIT License | |
| 544 | 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 | |
| 545 | Incorrect reference to a system-defined function in the routine body | Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:48 | MIT License | |
| 546 | NOT IN or <> ALL in routines | Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:42 | MIT License | |
| 547 | Perhaps Count(*) is wrongly used | Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes Count aggregate function like this - Count(*), uses outer join, and grouping. In case of grouping you do not want to get an answer that an empty group contains one member. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:40 | MIT License | |
| 548 | User-defined routines that produce a temporary table | Find user user-defined routines that produce a temporary table | General | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:25 | MIT License | |
| 549 | Do not create user-defined routines that have the same name as some installed extension routine | Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:14 | MIT License | |
| 550 | Overloading may cause runtime error | Routines in the same schema that have the same name and that have parameters with different types or different number of parameters are not considered to be in conflict at the creation time. However, if defaults are provided in the definition of parameters, then these routines might be conflict during runtime. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:11 | MIT License | |
| 551 | Routines with the same name and parameters in different schemas | Find user-defined routines with the same name and parameters (including the order of parameters) in different schemas. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:11 | MIT License | |
| 552 | Number of columns covered with constraints | For different types of constraints find the number of columns covered with constraints of such type. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2021-10-16 11:01 | MIT License | |
| 553 | Base tables with exactly one key | Find all base tables that have exactly one PRIMARY KEY or UNIQUE constraint. Find and enforce all the keys. Are you sure there are not more keys in the table? | General | system catalog base tables only | 2021-10-16 10:39 | MIT License | |
| 554 | All key constraints | Find all the primary key and unique constraints of base tables. | General | system catalog base tables only | 2021-10-16 10:37 | MIT License | |
| 555 | Primary key columns are not the first in a table | In SQL tables each column has the ordinal position. Find all the base tables where the primary key columns are not the first in the table, i.e., there is at least one non-primary key column that comes before a primary key column. It is easier to grasp the primary key if its columns are the first in the table. It could be that a table inherits from an abstract table where no keys have been defined. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-16 10:33 | MIT License | |
| 556 | Definition of a non-minimal superkey instead of a candidate key (based on key constraints) | Find primary/key unique constraints (sets of columns) that are proper subsets of other primary key/unique constraints of the same table. 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, i.e., the keys should not have redundancy in terms of columns. | Problem detection | system catalog base tables only | 2021-10-16 10:29 | MIT License | |
| 557 | Duplicate keys | Find completely overlapping key (primary key and unique) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns. | Problem detection | system catalog base tables only | 2021-10-16 10:27 | MIT License | |
| 558 | Base table columns for recording geographic coordinates that do not have a suitable type (based on column names) | Find base table columns that are according to the name meant for recording geographic coordinate but do not have a suitable type (numeric or point). | Problem detection | INFORMATION_SCHEMA only | 2021-10-08 12:01 | MIT License | |
| 559 | Base table columns for recording geographic coordinates (based on column names) | Find base table columns that are according to the name meant for recording coordinates. | General | INFORMATION_SCHEMA only | 2021-10-08 11:59 | MIT License | |
| 560 | Deferrable foreign key constraint with a RESTRICT compensating action | Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed. | Problem detection | system catalog base tables only | 2021-10-08 11:29 | MIT License | |