| 781 | All user triggers that are associated with tables | Find user-defined triggers that react to data modifications in tables. Triggers should be used only for the tasks that cannot be achieved in a declarative manner, i.e., by declaring a constraint. Triggers of the same table with the same event_manipulation, action_timing, and action_orientation are sorted based on the trigger name. This is the order of execution of triggers. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-01-19 11:27 | MIT License | |
| 782 | All user-defined TOAST-able types | Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). | General | system catalog base tables only | 2020-11-06 14:51 | MIT License | |
| 783 | User-defined derived tables | Find user-defined views and materialized views. Pay attention to the outer join operations. One should use these if and only if there is a real need for them. Otherwise they just reduce performance. On the other hand, pay attention that outer join is used where it is logically needed. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-12-11 15:23 | MIT License | |
| 784 | 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 | |
| 785 | 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 | |
| 786 | 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 | |
| 787 | Derived tables with ranking | Find views and materialized views that use rank and dense_rank window functions. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
| 788 | Derived tables with string_agg | Find views and materialized views that use string_agg aggregate function. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
| 789 | Too short or missing comments of derived tables and routines | Find views, materialized views, and user-defined routines that do not have a comment at all or the comment is shorter than twice the length of the object name, or the comment states that it is missing (TODO). You should give information to future developers and maintainers of the system (including the future version of yourself). Do not just repeat the name in the comment (with perhaps some rewording). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-19 12:56 | MIT License | |
| 790 | Updatable views that have not been turned to read only | Find views that are theoretically updatable but do not have INSTEAD OF trigger or DO INSTEAD NOTHING rule to prevent data modifications through the view. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-29 10:20 | MIT License | |
| 791 | Views without security barrier | Find views that do not have the security barrier option. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-22 12:05 | MIT License | |
| 792 | Views with WHERE but without security barrier | Find views that do not have the security barrier option but restrict rows in some way. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-01-14 12:58 | MIT License | |
| 793 | Views with security invoker | Find views that have the security invoker option. Such option is possible starting from PostgreSQL 15. In case of using such views one cannot create a system where data is accessed through views and the users (applications) do not have direct access to the base tables. | Problem detection | system catalog base tables only | 2023-11-13 12:20 | MIT License | |
| 794 | Derived table uses a function to get data from another table | Find views that use a function to get data from another table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-24 14:36 | MIT License | |
| 795 | Duplicate views | Find views with exactly the same subquery. There should not be multiple views with exactly the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
| 796 | Views with unnecessary security invoker | Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker." | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-17 18:39 | MIT License | |
| 797 | Non-foreign key columns that have no associated CHECK constraints | Find what are the base table columns that are not foreign key columns and that have no associated CHECK constraints? Perhaps some CHECK constraints are missing. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | |
| 798 | The same database object name is used repeatedly in case the same database object type | Find what database object names are used more than once in case the objects of the same type. If the names differ from each other only by digits or underscores, then consider these the same name. For instance, if there are base tables Person and Person2 (in the same schema or different schemas), then the query returns the name Person. Make sure that there is no duplication of implementation elements in the database. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-03-17 01:12 | MIT License | |
| 799 | Do not always depend on one's parent | Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. | General | system catalog base tables only | 2021-03-12 15:36 | MIT License | |
| 800 | Do not always depend on one's parent - column names are ot sufficiently different | Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four). | Problem detection | system catalog base tables only | 2022-11-26 17:19 | MIT License | |