PostgreSQL Query Catalog

Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows.

Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have NOT NULL constraint. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the columns mandatory.

Find user-defined routines where gen_salt function is used with the number of rounds that is smaller than 12. The number should be adjusted based on the hardware where the system resides. Password hashing should take at least 250 ms.

This query audits the schema for semantically weak column identifiers. It identifies columns named with generic nouns (e.g., data, info, value) or context-free technical terms (e.g., id, type, code, date, fk, pk). Such names violate the principle of self-documenting schema design, as they fail to describe the specific domain attribute being stored. To prevent ambiguity—especially in complex joins—column names should carry sufficient semantic context (e.g., product_type instead of type, creation_date instead of date).

This query generates a frequency distribution of semantically weak column identifiers within the schema. It aggregates the occurrences of generic names (e.g., id, type, data) to quantify their prevalence across the database. This statistical view serves as a prioritization tool for refactoring, highlighting the most ubiquitous violations of naming conventions that contribute to schema ambiguity.

This query calculates the total cardinality of semantically weak column identifiers across the entire schema, encompassing both base tables and views. It applies a uniform definition of "generic" (e.g., flagging identifiers like id, type, date) regardless of the underlying object type. By treating table and view columns equivalently, the query provides a holistic metric of naming ambiguity, quantifying the overall prevalence of non-descriptive attributes within the database's public interface.

Find column names in case of which the same table has another column (with more specific name) that name contains the column name in the end or in the beginning. For instance, a base table has columns parent and root_parent and the former col-umn name is too generic, i.e., it should be more specific.

This query identifies junction tables with semantically redundant names, specifically targeting those whose names contain the word "join". The structure of a many-to-many table (typically two foreign keys forming a composite primary key) is inherently declarative of its role as a join mechanism. Including the word "join" in the name is superfluous and violates the principle of naming based on the entity or relationship being modeled, not the implementation detail.

Find the names of parameters that are too generic. The routines work with data/information. Thus, it is not a good style to use generic words like data, information, parameter, etc. In the names of parameters. Moreover, avoid too generic parameter names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk, param.

Find parameter names in case of which the same routine has another parameter with the same mode but with more specific name, i.e., the name contains the parameter name in the end or in the beginning.

This query identifies columns in views (derived tables) whose names suggest they store a person's name but lack context about the person's role. In a view, a column containing a person's name should indicate their specific role regarding the entity. For example, a column simply named surname in an active_product view is ambiguous; a more descriptive name, such as registrar_surname, is preferred. However, if the primary purpose of the view is to present personal data, generic names like surname are perfectly acceptable.

"Avoid using the same word for two purposes. Using the same term for two different ideas is essentially a pun" (Robert C. Martin, Clean Code) Do not use the Estonian words like 'lisa', 'muuda', 'kustuta' or the corresponding English words 'add', 'delete', 'update' as the names of routines because it would make the code much less understandable. What is the task of the routine f_add? Register people? Register orders? What? Do not refer to the generic concepts like data and information because all routines work with these.

This query identifies tables with semantically weak, generic names that violate schema design best practices. It flags tables with name components such as "table", "data", "information", or "list". The principle is that a table name should accurately represent the real-world entity it models. Using generic nouns obscures the schema's meaning, reduces readability, and forces developers to inspect the table's contents to understand its purpose.

Find patterns of regular expressions where more than \ is written instead of \, e.g., \\s is used instead of \s to refer to a character class.

"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The names should be meaningful and searchable. Find the names (identifiers) of user-defined database objects that are shorter than three characters.

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

Find derived tables (views, materialized views) that are based on more than five tables and that have more than 15 columns. This view might produce "a denormalized world view" where all the data is together in one table and applications make queries based on this single view to fulfill their specific tasks. Such view does not follow the separation of concerns principle.

Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (contains "_to_").

This query performs a semantic analysis of trigger function comments to identify potential violations of separation of concerns. It flags triggers whose documentation references explicit database operations (e.g., OP1, OP2) but lacks terminology associated with validation or invariant enforcement (e.g., "check", "ensure", "validate"). This linguistic pattern suggests that the trigger may be improperly implementing the business operation itself (a side effect) rather than serving its primary role as an integrity guardrail, or that the documentation inaccurately reflects the trigger's behavior.

Find trigger functions that contain a conditional (IF or CASE) but do not contain a SELECT statement before these. The latter condition is for the reason that one cannot use a subquery in the WHEN clause. Thus, if one wants to make a query and decide the further action based on the results of the query, then one must do it within the body of the function.