| 161 | Patterns of the names of columns of simple primary keys | Find the patterns of the names of columns of simple primary keys. Make sure that the naming is consistent. Ideally, the names should indicate as to whether the column is a surrogate or a natural key column. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog. | General | system catalog base tables only | 2023-01-20 13:40 | MIT License | |
| 162 | Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column | Find patterns of the names of constraints and indexes. Make sure that the naming is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-01-14 20:52 | MIT License | |
| 163 | Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with two or three columns | Find patterns of the names of constraints and indexes. Make sure that the naming is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-01-14 20:52 | MIT License | |
| 164 | Patterns of the names of triggers and rules | Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-01-14 20:51 | MIT License | |
| 165 | Perhaps IS DISTINCT FROM should be used instead of <> in WHEN clauses | Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. | General | INFORMATION_SCHEMA only | 2020-12-24 14:30 | MIT License | |
| 166 | PL/pgSQL routines that use a cursor | Working with sets of rows rather than processing each row separately is more effective. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:54 | MIT License | |
| 167 | Polymorphic routines | Find routines that have a parameter (input or output) that can have values from different types. This parameter has one of the PostgreSQL polymorphic types. The set of polymorphic types in PostgreSQL is a proper subset of its pseudo-types. The use of such a parameter allows a single routine definition to operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 13:12 | MIT License | |
| 168 | Potentially a classifier is missing (based on field sizes) | Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-03-10 13:07 | MIT License | |
| 169 | Prefixes in the names of database objects | Find for different types of database objects all the prefixes that are used in different names. One should be consistent in naming, including in the use of prefixes. If you use prefix in the name of a database object, then it could refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-12-22 12:39 | MIT License | |
| 170 | Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers | Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that are not for the owner and not for the systemic user postgres. Make sure that there is the right amount of privileges for each and every relevant user. | General | system catalog base tables only | 2021-03-07 20:57 | MIT License | |
| 171 | Privileges to execute routines | Find privileges to execute routines that have been given to non-superusers. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to modify data in the database (in case of table functions read data), then they must execute a routine. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-29 10:38 | MIT License | |
| 172 | Privileges to use base table columns | If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-29 10:38 | MIT License | |
| 173 | Privileges to use base tables | Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-29 10:38 | MIT License | |
| 174 | Privileges to use views | Find privileges to use views. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to read data from a database, then they should use views. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-29 10:38 | MIT License | |
| 175 | Publicly accessible system catalog tables | Find privileges to use system catalog base tables or views that have been granted to public. | General | INFORMATION_SCHEMA only | 2023-11-24 14:56 | MIT License | |
| 176 | Routines that can be invoked with a variable number of arguments | Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:37 | MIT License | |
| 177 | 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 | |
| 178 | Routines with type casting | Make sure that your parameters have appropriate types in order to avoid unnecessary type casting. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:42 | MIT License | |
| 179 | Rules with the same name in different schemas | Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication. | General | system catalog base tables only | 2020-11-06 14:51 | MIT License | |
| 180 | Set operations that do not remove duplicate rows in derived tables | Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-10 13:32 | MIT License | |