PostgreSQL Query Catalog

This query identifies a potential domain modeling flaw where multiple distinct entity tables reference a single, shared state classifier table. According to robust design principles, each main entity type should define its own independent state machine and lifecycle. Sharing a classifier creates undesirable coupling; even if the state vocabularies (e.g., 'Active', 'Inactive') appear identical currently, the business logic for distinct entities is likely to diverge over time. Furthermore, reliance on a universal state table often indicates an under-analyzed domain model utilizing overly generic state transitions.

This query identifies a potential schema mismatch regarding data precision. It flags base table columns that default to CURRENT_USER or SESSION_USER but define a character length differing from the PostgreSQL standard identifier limit (typically 63 bytes, defined by NAMEDATALEN - 1).

Risk (Length < 63): Poses a hard runtime failure risk if a username exceeds the defined length. Inefficiency (Length > 63): Indicates imprecise modeling, as the stored value can technically never exceed the system limit.

This query identifies a semantic mismatch between a column's name and its data type. It flags base table columns that adhere to a predicate-based naming convention (i.e., starting with is_, has_, can_, or on_) but are not defined with the BOOLEAN data type. This is a design flaw as it forces developers to infer and manage truthiness through other types (e.g., INTEGER, CHAR(1)), which undermines schema clarity, requires data type coercion in queries, and can compromise data integrity by permitting non-boolean states.

This query identifies a semantic mismatch between column definitions and their identifiers. It flags base table columns that are configured with a DEFAULT value of CURRENT_USER or SESSION_USER (indicating they store user identity) but whose names fail to reflect this purpose. Specifically, it searches for columns lacking semantic cues such as "user", "login", "owner", or "by" in their names. This obscuration reduces schema self-documentation, as developers cannot intuitively determine that the column is intended for audit or ownership tracking.

This query identifies a semantic mismatch between data types and constraint logic. It targets base and foreign table columns that are defined with non-textual data types (e.g., INTEGER, DATE, BOOLEAN) but are subject to single-column CHECK constraints utilizing string pattern matching operators (LIKE, SIMILAR TO, or regular expressions). This practice forces implicit casting to text, which is computationally inefficient and indicates a design flaw. It suggests that either the column should utilize a textual data type, or the constraint should be rewritten using operators appropriate for the actual data type (e.g., numeric ranges instead of regex).

This query identifies a semantic mismatch between the name and implementation of CHECK constraints (on tables, foreign tables, or domains). It targets constraints whose names suggest they validate against whitespace-only strings (e.g., names containing 'whitespace', 'space', 'blank'), but whose logic inappropriately uses the trim() function. The trim() function is a formatting tool for removing leading/trailing spaces, not a validation tool for ensuring a string is not composed entirely of whitespace. This indicates a likely implementation error, as a more robust regular expression (e.g., column !~ '^\s*$') is the correct tool for this type of validation.

This query identifies a semantic mismatch in data type selection for columns intended to store measurement data. It targets columns whose names imply a quantitative measurement (e.g., "length", "weight", "count", excluding boolean prefixes like "is_") but are not defined with a numeric data type (INTEGER, NUMERIC, etc.). Storing measurements as text (VARCHAR) prevents mathematical operations, aggregation, and proper sorting, and is considered a design flaw.

This query identifies a semantic mismatch in data type selection for columns intended to store telephone numbers. It flags columns whose identifiers imply phone number content (e.g., names containing "phone", "mobile", "telef") but are defined with non-textual data types (e.g., INTEGER, NUMERIC, BIGINT). Telephone numbers are semantically strings, as they may contain leading zeros, international prefixes (+), and formatting characters (-, (), ext.), and are not subject to arithmetic operations. Storing them as numeric types leads to data loss (truncation of leading zeros) and formatting inflexibility.

This query identifies a semantic mismatch in data types for columns intended to store standard codes, such as country, language, currency, or airport codes. It flags columns whose names suggest they contain these codes, but which are defined using non-text data types (e.g., integer, numeric, bigint). Since these codes often contain letters or leading zeros and are not used in mathematical calculations, they are semantically strings. Storing them as numeric types can lead to data loss—such as the truncation of leading zeros—and reduces formatting flexibility.

This query identifies automatically updatable views that define a row restriction (via a WHERE clause) but lack the WITH CHECK OPTION constraint. In the absence of this constraint, it is possible to perform INSERT or UPDATE operations through the view that result in rows satisfying the base table constraints but failing the view's inclusion criteria. This leads to "phantom updates," where the modified data is committed to the database but immediately disappears from the view's scope. Enforcing WITH CHECK OPTION ensures that all modifications performed through the view respect its defining predicate.

This query identifies automatically updatable views that lack the WITH CHECK OPTION clause. Without this constraint, it is possible to perform INSERT or UPDATE operations through the view that create rows which do not satisfy the view's defining predicate (the WHERE clause). This results in "phantom" modifications where the new or updated data is successfully committed to the base table but is immediately excluded from the view's result set. Enforcing WITH CHECK OPTION ensures that all data modifications performed through the view remain visible within the view.

This query identifies base table columns and domains that utilize CHECK constraints to simulate boolean logic on non-boolean data types. It targets constraints that restrict the domain of permitted values to binary sets, such as {0, 1}, {'Y', 'N'}, {'T', 'F'}, or string literals like 'true'/'false'. While functional, this "pseudo-boolean" pattern is considered suboptimal in PostgreSQL. The native BOOLEAN data type is preferred for its storage efficiency, semantic clarity, and built-in support for logical operators, rendering such manual constraints unnecessary.

This query identifies base table columns defined as unbounded TEXT or VARCHAR (without a length specifier) that lack any corresponding CHECK constraint to restrict value length. It explicitly excludes foreign key columns and columns heuristically identified as descriptive fields (e.g., names containing "comment", "description", "note"), where arbitrary length is typically acceptable. For structured attributes (such as names, codes, or identifiers), relying on unbounded types without constraints is a design risk, potentially allowing excessive data payload, complicating index usage, and violating domain constraints.

This query identifies base table columns designated for storing address components (e.g., IP addresses, emails, telephone numbers, or physical locations) that have inappropriate length constraints. It flags columns where the defined field size is either too strict or too loose compared to real-world data requirements. Operating on a heuristic basis, the query targets columns whose names imply address data (e.g., containing 'addr' or 'mail') but whose definitions fail to align with standard lengths. Highlighting both insufficient allocation (truncation risk) and unbounded allocation (data quality risk) helps ensure that these fields are sized according to domain standards, which is crucial for data integrity and usability.

This query identifies base table columns designated for storing codes (e.g., vin and isbn codes) that lack appropriate length constraints reflecting real-world data requirements. It operates on a heuristic basis, targeting columns whose identifiers imply code data (e.g., names containing "isbn" or "vin") but whose definitions fail to account for standard maximum lengths. This includes both insufficient allocation (truncation risk) and unbounded allocation (data quality risk). Ensuring these fields are sized according to domain standards is crucial for data integrity and usability.

This query identifies base table columns intended to store IP or network addresses that are not using PostgreSQL's native inet data type. It locates these columns by searching for specific English and Estonian naming patterns (such as 'ip', 'network', 'addr', or 'aadr'). Storing network addresses as generic text is an anti-pattern, as the inet type provides built-in validation, specialized network functions, and efficient indexing.

This query identifies base table columns that, based on their name, are presumed to store personal names but lack an explicit maximum length constraint. It operates on a heuristic, flagging columns with names like first_name, surname, etc., that are defined with unbounded textual types (e.g., text, varchar) and have no corresponding CHECK constraint to limit their length (e.g., char_length(col) <= n). The absence of such a limit is a design flaw that can introduce usability issues in front-end applications and create potential security vulnerabilities related to excessive data submission.

This query identifies base table columns that unnecessarily include the table name. It searches for columns that are not part of a primary or foreign key and contain the name of their parent table. To avoid flagging legitimate naming conventions, it explicitly excludes a list of generic column names (e.g., name, description, nimi, kommentaar) where prefixing with the table name is considered good practice for improving clarity in queries.

This query identifies base table columns whose name and data type suggest they store row registration or last modification times, but which lack a default value. In good database design, such audit timestamp columns should typically have a default value (like CURRENT_TIMESTAMP or now()) to ensure the time is recorded automatically.

This query identifies base table columns whose names suggest they store national identification numbers (personal codes), specifically targeting those that serve as a primary or unique key. Using a national ID as a standalone key enforces a highly restrictive business rule: it assumes that all individuals in the system come from a single country, as identical ID numbers can exist in different countries. The query flags these instances to prompt a review, ensuring that this single-country limitation is intentional and actually aligns with the domain rules.