| 741 | SET NULL compensatory action is unsuitable | Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 742 | Short cycle (columns) | Find cases where two candidate keys of the same table that are also foreign keys reference to each other. | Problem detection | system catalog base tables only | 2021-11-28 02:08 | MIT License | |
| 743 | Short cycles (tables) | Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-27 20:52 | MIT License | |
| 744 | Should the time zone be recorded in case of time or not? | Find all the base table columns that have the type time without time zone or time with time zone. Return the data only if there is at least one column with the type time without time zone and one column with the type time with time zone. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | |
| 745 | Should the time zone be recorded in case of timestamp or not? | Find all the base table columns that have the type timestamp without time zone or timestamp with time zone. Return the data only if there is at least one column with the type timestamp without time zone and one column with the type timestamp with time zone. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | |
| 746 | Simple check constraints with multiple tasks | Find simple check constraints, i.e., check constraints that cover one column that seem to have multiple tasks. The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-01-01 11:42 | MIT License | |
| 747 | Simple primary keys that column name does not contain the table name | Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent. | Problem detection | system catalog base tables only | 2023-03-19 10:35 | MIT License | |
| 748 | Some candidate key values cannot be used as foreign key values | Find foreign key constraints in case of which some candidate key values cannot be used as foreign key values. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-10 12:13 | MIT License | |
| 749 | Some CHECKS are associated with a domain and some with the base table columns that have the domain | Find cases where some CHECKS are associated with a domain and some with the base table columns that have the domain. Avoid duplication of code. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring CHECKS at the level of the domain and not at the level of base table columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
| 750 | Some data modification functions return a value and some not | Find as to whether there are data modification routines that return a value as well as data modification routines that do not return a value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-29 16:47 | MIT License | |
| 751 | Sometimes regexp_like, sometimes ~ | Find as to whether you sometimes use regexp_like function and sometimes ~ operator. These implement the same functionality. regexp_like function that was added to PostgreSQL 15 and provides the same functionality as ~ and ~* operators. Try to be consistent. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-12-13 13:23 | MIT License | |
| 752 | Sorting rows based on random values in derived tables | Find derived tables (views and materialized views) that sort 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-02-25 17:29 | MIT License | |
| 753 | Sorting rows based on random values in derived tables without limiting rows | Find derived tables (views and materialized views) that sort 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-02-25 17:30 | MIT License | |
| 754 | 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 | |
| 755 | 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 | |
| 756 | SQL function does not return a value | Find SQL functions that do not return a value (return VOID) but the SQL statement in the function has RETURNING clause. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-02-27 17:28 | MIT License | |
| 757 | SQL functions that use optimistic approach for locking but do not return a value | Find SQL functions that use a hidden column of PostgreSQL tables - xmin - to implement optimistic locking but do not return any information to the invoker of the functions, i.e., whether the update/delete operation succeeded or not. The functions should let their invokers know as to whether the function succeeded in updating or deleting a row. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 13:06 | MIT License | |
| 758 | STATEMENT level triggers and ROW level AFTER triggers without RETURN NULL | Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 759 | STATEMENT level triggers that refer to the values of row variables NEW or OLD | Find STATEMENT level triggers that refer to the values of row variables NEW or OLD. NEW and OLD are special variables that can only be used in row-level trigger procedures. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 14:23 | MIT License | |
| 760 | Stating the obvious | Find database objects that name contains words "data" or "info". These are noise words because databases are meant for storing and manipulating data/information. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-03-17 10:37 | MIT License | |