PostgreSQL Query Catalog

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.

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

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

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

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

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.

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).

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.

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.

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.

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.

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.

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.

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.

Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the 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.

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.

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.

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.

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.