Filter Queries

Found 127 queries.

Page 1 of 7
  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets, it shouldn't be a problem in case of the relatively small amount of data in the system catalog of a database.

#1. All derived tables that use joining tables

INFORMATION_SCHEMA+system catalog base 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 License: MIT (opens in new tab)

#2. AND takes precedence over OR

INFORMATION_SCHEMA+system catalog base tables

Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator.

General License: MIT (opens in new tab)

#3. A non-parameterized table function instead of a view

INFORMATION_SCHEMA+system catalog base tables

Find table functions that do not have any parameters. Prefer simpler and more portable solutions.

Problem detection License: MIT (opens in new tab)

#4. A predefine character class has been incorrectly specified

INFORMATION_SCHEMA+system catalog base tables

Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:].

Problem detection License: MIT (opens in new tab)

#5. A table has the same name as a routine

INFORMATION_SCHEMA+system catalog base tables

Find table names that are the same as some routine name. Use different names to avoid confusion.

Problem detection License: MIT (opens in new tab)

#6. Avoid using length function

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#7. Case-insensitive regular expressions (~* or !~*) containing both uppercase and lowercase letters

INFORMATION_SCHEMA+system catalog base tables

This query identifies database objects (table and domain CHECK constraints, trigger WHEN conditions, rules, views, materialized views, and user-defined routines) that use a case-insensitive regular expression operator (~* or !~*), yet contain both uppercase and lowercase letters inside the regular expression pattern (for example, col ~* '^[A-Z][a-z]+$', col ~* '^[ab].*[C-D]', or col ~* '^[a-zA-Z0-9]+$').

Combining a case-insensitive operator with a mixed-case regular expression pattern indicates one of two issues:


  • Incorrect operator (broken case validation): The developer intended to enforce distinct case rules for different parts of the string (such as requiring an uppercase letter in one position and a lowercase letter in another, e.g., '^[C].*[a-z]' or '^\$2[abxy]\$[0-9]{2}\$[./A-Za-z0-9]{53}$'), but mistakenly used ~* (or !~*) instead of the case-sensitive operator ~ (or !~). Because ~* disables case sensitivity across the entire pattern, the intended case restrictions are silently bypassed.

  • Redundant or inconsistent pattern style: The developer genuinely intended case-insensitive matching, but redundantly specified both letter cases (such as a-zA-Z) or mixed uppercase and lowercase characters inconsistently within the pattern.

Problem detection License: MIT (opens in new tab)

#8. Case insensitive search

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#9. Columns of derived tables that name has been given by the system

system catalog base tables only

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 License: MIT (opens in new tab)

#10. Columns of derived tables that name has been given by the system (2)

system catalog base tables only

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 License: MIT (opens in new tab)

#11. Comments of derived tables

system catalog base tables only

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 License: MIT (opens in new tab)

#12. Coverage by derived tables

INFORMATION_SCHEMA+system catalog base 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 License: MIT (opens in new tab)

#13. Database objects of the same type and case insensitive name in the same container

INFORMATION_SCHEMA+system catalog base tables

This query identifies database objects of the same type within the same schema (or container) that have identical names (and parameters in case of routines) when ignoring case. In SQL databases, this confusing scenario typically occurs when mixing quoted (case-sensitive) and unquoted (case-insensitive) identifiers. For example, a single schema could contain both a table named "Client" and another table named client.

Problem detection License: MIT (opens in new tab)

#14. Database object that belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#15. Derived table names have prefix or suffix

system catalog base tables only

This query identifies views and materialized views that include a prefix or suffix in their names (such as v_ or _mv). In a well-designed database, derived tables should follow the exact same naming conventions as base tables, because they function identically from the application's perspective. Therefore, if base tables do not use type-based prefixes or suffixes, derived tables should not use them either.

Problem detection License: MIT (opens in new tab)

#16. Derived table on top of another derived table

INFORMATION_SCHEMA+system catalog base tables

Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables.

Problem detection License: MIT (opens in new tab)

#17. Derived table presents the same data in the same way as a single base table

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#18. Derived tables that have a column with the xid type

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#19. Derived tables that present data in json or xml format

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#20. Derived tables with embedded row locking

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)
# Name(sorted ascending, activate to sort descending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(activate to sort ascending) Last update(activate to sort ascending) License Actions
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 MIT (opens in new tab) 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 MIT (opens in new tab) 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 MIT (opens in new tab) 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 MIT (opens in new tab) 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 MIT (opens in new tab) 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 MIT (opens in new tab) View
7 Case-insensitive regular expressions (~* or !~*) containing both uppercase and lowercase letters This query identifies database objects (table and domain CHECK constraints, trigger WHEN conditions, rules, views, materialized views, and user-defined routines) that use a case-insensitive regular expression operator (~* or !~*), yet contain both uppercase and lowercase letters inside the regular expression pattern (for example, col ~* '^[A-Z][a-z]+$', col ~* '^[ab].*[C-D]', or col ~* '^[a-zA-Z0-9]+$').

Combining a case-insensitive operator with a mixed-case regular expression pattern indicates one of two issues:

  • Incorrect operator (broken case validation): The developer intended to enforce distinct case rules for different parts of the string (such as requiring an uppercase letter in one position and a lowercase letter in another, e.g., '^[C].*[a-z]' or '^\$2[abxy]\$[0-9]{2}\$[./A-Za-z0-9]{53}$'), but mistakenly used ~* (or !~*) instead of the case-sensitive operator ~ (or !~). Because ~* disables case sensitivity across the entire pattern, the intended case restrictions are silently bypassed.

  • Redundant or inconsistent pattern style: The developer genuinely intended case-insensitive matching, but redundantly specified both letter cases (such as a-zA-Z) or mixed uppercase and lowercase characters inconsistently within the pattern.

Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
8 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 MIT (opens in new tab) View
9 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 MIT (opens in new tab) View
10 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 MIT (opens in new tab) View
11 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 MIT (opens in new tab) View
12 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 MIT (opens in new tab) View
13 Database objects of the same type and case insensitive name in the same container This query identifies database objects of the same type within the same schema (or container) that have identical names (and parameters in case of routines) when ignoring case. In SQL databases, this confusing scenario typically occurs when mixing quoted (case-sensitive) and unquoted (case-insensitive) identifiers. For example, a single schema could contain both a table named "Client" and another table named client. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
14 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 MIT (opens in new tab) View
15 Derived table names have prefix or suffix This query identifies views and materialized views that include a prefix or suffix in their names (such as v_ or _mv). In a well-designed database, derived tables should follow the exact same naming conventions as base tables, because they function identically from the application's perspective. Therefore, if base tables do not use type-based prefixes or suffixes, derived tables should not use them either. Problem detection system catalog base tables only MIT (opens in new tab) View
16 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 MIT (opens in new tab) View
17 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 MIT (opens in new tab) View
18 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 MIT (opens in new tab) View
19 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 MIT (opens in new tab) View
20 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 MIT (opens in new tab) View