PostgreSQL Query Catalog

This query audits the database schema to enforce a uniform naming strategy for CHECK constraints. It identifies inconsistencies where constraints enforcing identical Boolean expressions are named using disparate patterns across different tables. To isolate the naming pattern from specific object identifiers, the query normalizes the constraint names by substituting the actual table name with the generic token TABLE. This allows it to detect violations of the "Clean Code" principle of consistency—flagging cases where the same logical rule is implemented with a specific naming convention in one context (e.g., chk_TABLE_column) but a different convention in another (e.g., TABLE_column_check).

Find the names that are used in case of different types of elements

Do not cause a potential performance bottleneck by having a shared resource. By having a shared sequence it is not possible to change properties of sequences of different tables independently (for instance the owner column or step), i.e., it increases coupling between tables. By having a shared sequence it is impossible to specify the owner (table column) to the sequence generator.

Find SQL-language routines that have the body that is string literal, i.e., the body is not SQL-standard function body. Routines with a SQL-standard body are permitted starting from PostgreSQL 14.

Find base table columns that have Boolean type and 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 Boolean columns mandatory.

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.

This query identifies logically flawed CHECK constraints on columns with timezone-unaware data types (DATE and TIMESTAMP WITHOUT TIME ZONE). It specifically targets constraints that contain timezone-aware logic (e.g., using AT TIME ZONE). This represents a critical semantic mismatch: the data type stores a "naive" or "local" time with no timezone context, while the constraint attempts to evaluate it within a specific timezone. The outcome of such a check is non-deterministic, as it depends on the session's current TimeZone setting, leading to unpredictable and unreliable data validation. This is a design flaw; if timezone-aware logic is required, the column should use the TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) data type.

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.

Find candidate key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix. These are too generic names.

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.

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.

Find domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1").

Find foreign key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix that describes the nature of relationship with the referenced table or at least referes to the referenced table. These are too generic names.

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.

Find table constraints (constraints that are associated directly with the table) that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name.