| 581 |
Non-predefined character classes must not be between double square brackets |
Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2023-11-13 12:32 |
MIT License |
View |
| 582 |
Non-updatable views that have data modification privileges |
Be precise and do not give impossible privileges. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:29 |
MIT License |
View |
| 583 |
Non-updatable views with DO INSTEAD NOTHING rules |
Find non-updatable views that have a DO INSTEAD NOTHING rule. The rule is used to prevent updates. However, the view is aniway non-updatable. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2023-10-28 13:01 |
MIT License |
View |
| 584 |
No point to have in a procedure COMMIT without ROLLBACK or vice versa |
If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 12:00 |
MIT License |
View |
| 585 |
Not equals check in unstandardized way |
Find user-defined routines that use != operator to test as to whether two values are not equal. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2023-12-13 14:16 |
MIT License |
View |
| 586 |
Not inherited CHECK constraints that are recreated in the immediate subtable |
Find base table CHECK constraints that have been defined as NOT INHERITED but the constraint with the same Boolean expression has been defined in the immediate subtable of the table. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 587 |
Not inherited CHECK constraints that cover at least one column |
Find CHECK constraints that cover at least one column and that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 588 |
NOT IN or <> ALL in derived tables |
Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2024-12-24 13:50 |
MIT License |
View |
| 589 |
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 |
2024-12-24 13:50 |
MIT License |
View |
| 590 |
NOT NULL constraint is directly associated with a column instead of the domain of the column |
Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2023-10-20 15:34 |
MIT License |
View |
| 591 |
NOT NULL constraint via CHECK instead of NOT NULL constraint |
Find columns where the NOT NULL constraint has been added with the help of an explicit CHECK constraints instead of a NOT NULL constraint. One should note that internally NOT NULL constraints are treated as a kind of CHECK constraints. Nevertheless, the design should be consistent (i.e., NOT NULL is enforced with the same way in different places). |
Problem detection |
INFORMATION_SCHEMA only |
2024-12-12 09:42 |
MIT License |
View |
| 592 |
NOT VALID foreign key constraints |
Find not valid foreign key constraints. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint. |
Problem detection |
system catalog base tables only |
2021-02-25 17:30 |
MIT License |
View |
| 593 |
Numeric literals between apostrophes |
Placing numeric literals between apostrophes will cause unnecessary type conversions. It could also be that the literal should indeed be textual but the problem is in choosing the values. For instance, table Occupation has column occupation_code with the type VARCHAR(3). However, all the values in the column consist of digits (for instance, 1, 2, 3). Thus, it would have been better to a) use SMALLINT as the column type or b) use different occupation codes that contain additional symbols to digits. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 13:20 |
MIT License |
View |
| 594 |
ON DELETE CASCADE is not needed (based on classifier tables) |
Find foreign key constraints with ON DELETE CASCADE compensating action that refer to classifier (reference data) tables. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2023-12-08 14:52 |
MIT License |
View |
| 595 |
ON DELETE CASCADE is probably missing (based on the multiplicity of the relationship) |
Find foreign key constraints that completely overlap with a candidate key constraint (primary key or unique constraint) but the foreign key constraint does not have the ON DELETE CASCADE compensating action. In this case there is a foreign key that implements a relationship type between a strong entity type and a weak entity type (1-1 relationship type). Therefore, in this case ON DELETE CASCADE is an appropriate compensating action. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 596 |
ON DELETE CASCADE is probably not needed (based on the relationship type) |
Find foreign key constraints that implement a non-identifying relationship type and have ON DELETE CASCADE compensating action. If the identity of the parent table is not a part of the identity of the child table, then there is a non-identifying relationship type and most probably the foreign key should not have ON DELETE CASCADE. |
Problem detection |
system catalog base tables only |
2023-10-28 18:38 |
MIT License |
View |
| 597 |
ON DELETE SET NULL is probably missing |
Find implementations of the adjacency list design pattern, where the corresponding foreign key columns are optional but the foreign key constraint does not have ON DELETE SET NULL compensating action. Implement adjacency list correctly. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 598 |
One true lookup table |
Find tables that contain all (or most) of the classifier values and tables that refer to these. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 599 |
Only ID primary key |
Find base base tables have the simple primary key that contains a column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. In addition the base table must not have any unique constraint. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2022-11-09 15:15 |
MIT License |
View |
| 600 |
Only one value permitted in a base table or a foreign table column (based on enumeration types) |
Find columns of base tables or foreign tables in case of which the type of the column permits only one value in the column. The type is an enumeration type that specifies only one value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |