| 1 |
All derived tables that use joining tables |
This query identifies complex derived tables (views and materialized views) that perform data integration operations. Specifically, it filters for views whose definition involves joining two or more distinct tables. This distinguishes non-trivial views—which encapsulate relationship logic and data aggregation—from simple projection views that merely mirror a single base table. The result highlights the core reporting and data integration layer of the schema. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-26 10:02 |
MIT License |
View |
| 2 |
AND takes precedence over OR |
Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 3 |
A non-parameterized table function instead of a view |
Find table functions that do not have any parameters. Prefer simpler and more portable solutions. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 4 |
A predefine character class has been incorrectly specified |
Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:]. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 5 |
A table has the same name as a routine |
Find table names that are the same as some routine name. Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 6 |
Avoid using length function |
This query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-13 12:53 |
MIT License |
View |
| 7 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 8 |
Columns of derived tables that name has been given by the system |
Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 9 |
Columns of derived tables that name has been given by the system (2) |
Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 10 |
Comments of derived tables |
Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 11 |
Coverage by derived tables |
Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 12 |
Database objects of the same type and case insensitive name in the same container |
Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 13 |
Database object that belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ |
Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 14 |
Derived table names have prefix or suffix |
Find the names of views and materialized views that have prefix or suffix. Follow the same naming style as in case of base tables (derived tables are also tables). Thus, if base tables do not have prefixes or suffixes, then derived tables shouldn't have these as well. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 15 |
Derived table on top of another derived table |
Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 16 |
Derived table presents the same data in the same way as a single base table |
Find derived tables (views and materialized views) that present data from one base table without adding columns, renaming columns, changing the order of columns, removing columns, or restricting rows. Perhaps something is missing from the subquery of the derived table. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 17 |
Derived tables that have a column with the xid type |
Find the derived tables (views and materialized views) that have a column with the xid type, i.e., these use the data from the hidden xmin column of a base table. If one uses optimistic approach for dealing with the concurrent data modifications, then xmin values should be presented by views and used in routines that modify or delete rows. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 18 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 19 |
Derived tables with embedded row locking |
This query identifies derived tables (views and materialized views) whose defining subqueries utilize explicit row locking clauses (e.g., FOR UPDATE, FOR SHARE). Embedding locking semantics within a view definition is considered an architectural anti-pattern. It couples data projection with transaction control, causing simple read operations against the view to unexpectedly acquire locks. This behavior degrades concurrency by blocking other readers and violates the principle that reading a data element should not implicitly block simultaneous access. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 12:58 |
MIT License |
View |
| 20 |
Derived tables with multiple DISTINCT's |
Find derived tables (views and materialized views) that contain more than one DISTINCT invocation. Make sure that the query is correctly written, including that it does not have unwanted formation of a Cartesian product. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |