PostgreSQL Query Catalog

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.

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 views that use a function to get data from another table.

Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input.

Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions.

Find the cases when the names of columns in different foreign keys that reference to the same candidate key are different. If different names reflect different roles, then it is legitimate. However, there could also be accidental differences that makes it more difficult to use the database.

The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different prefixes. Thus, for instance, one cannot use USING syntax for joining the tables.

This query identifies naming inconsistencies in foreign key relationships by comparing the suffixes of foreign key columns against their referenced candidate key columns. It flags pairs where the suffixes diverge (e.g., referencing user_id via a column named user_code or user_ref). Such discrepancies prevent the use of the simplified ANSI SQL USING syntax for joins, forcing the use of explicit ON clauses. The goal is to enforce a standardized suffix convention (typically _id) across the schema to improve maintainability and query readability.

Find all the default values of base table, view, and foreign table columns that are expressions invoking a function that returns a timestamp. Do it only if there are different expressions, i.e., there could be possible inconsistencies.

Identify disabled rules. These should be enabled or dropped, otherwise these are dead code.

These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work.

Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code.

Find domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity.

This query identifies violations of modular design principles within user-defined domains. It detects CHECK constraints belonging to a domain where the constraint's name incorporates the identifier of a specific base table that utilizes that domain. Since domains are intended as reusable, abstract data types applicable across multiple entities, embedding a specific implementation context (like a table name) into the constraint's identifier creates semantic coupling. This makes the domain confusing to reuse in other contexts. Constraint identifiers should reflect the inherent logic of the domain (e.g., check_email_format), not the object to which it is applied.

Find domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains.

Find domains that declare a default value and that are used in case of multiple foreign key constraints that point to different tables. Domains should be used in a manner that does not cause unnecessary coupling of concerns. For instance, let us assume that columns client_state_type_code of table Client (that is used to implement the relationship with table Client_state_type) and worker_state_type_code of table Worker (that is used to implement the relationship with table Worker_state_type) have been defined based on the same domain. It the domain has a default value, then it determines the initial state of both clients and workers. However, it must be possible to determine the initial state independently in case of clients and workers.

Use different names to avoid confusion.

Find names of domains that contain the name of the table that column the domain specifies. Domains are reusable artifacts that one should be able to use in case of different base tables. Thus, it is inappropriate to use the name of a particular table in the name of the domain.