PostgreSQL Query Catalog

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 optional (nullable) timestamp columns in base tables that lack a default value. It highlights fields that might represent open-ended time periods (such as expiration or end dates). In such cases, it is often a better practice to assign the special value 'infinity' as the default, rather than relying on NULL values.

This query identifies boolean columns in base tables that lack a default value, but whose names suggest they implement a state machine or act as state flags. In good database design, such boolean state indicators should typically have a default value (false or true) to ensure a predictable initial state when new records are created.

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 inconsistencies in two-column CHECK constraints across the database. Specifically, it flags cases where different tables share the exact same pair of columns, but the logical expressions governing them differ. For example, one table might enforce last_change_time >= reg_time, while another strictly enforces last_change_time > reg_time. Highlighting these discrepancies helps ensure uniform data validation rules.

This query identifies foreign key relationships that are established using name columns rather than standard identifiers. In a well-designed database, relationships should be built upon stable natural keys (such as codes) or system-generated surrogate keys, rather than mutable textual names.

This query identifies central classifier (reference) tables that store all or most of the system's reference values, along with the dependent tables that reference them.

This query identifies short text columns in base tables that are not part of any primary or foreign key, but whose names closely match an existing table in the database. This pattern suggests that the similarly named table might be a classifier (reference) table, and the flagged column should ideally have a foreign key constraint referencing it.

This query identifies user-defined database objects that share a common container and base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2). To avoid false positives—such as domains like d_name_50 and d_name_100 where the number signifies a length—the query employs a specific heuristic. It assumes a sequence starts with 1, 2, and 3. By removing these numbers from object names, it checks if multiple objects of the same type and base name result within the same container. A positive match strongly implies an intentional, sequential numbering. This pattern indicates a denormalized design, which complicates querying and is difficult to scale. The correct approach is to normalize the schema by creating a separate table for the repeating attribute.

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.

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.

This query checks whether columns storing standardized data—such as email addresses, phone numbers, IP addresses, postal codes, physical addresses, and file paths—have inconsistent field sizes across different tables. Identifying these discrepancies helps maintain a uniform database schema.

This query identifies columns in base and foreign tables that are intended to store age values. It detects these fields by analyzing both their naming conventions and data types.

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 state, type or category code columns that use unnecessarily large numeric data types, specifically integer or bigint. It locates these columns by matching specific naming patterns in English and Estonian. Since reference tables typically contain a limited number of rows, these code columns should ideally be defined as smallint to optimize storage space and improve performance.

This query identifies columns intended to store registration or modification timestamps that are currently defined as optional (nullable). Since creation and update times are crucial for data auditing, these columns should typically have a NOT NULL constraint.

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 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 detects data attributes that function as implicit classifiers but lack a corresponding reference entity. It targets textual columns that are not currently constrained (no PK, UK, or FK) but exhibit characteristics of coded data: they either have a very short length (≤ 3 characters) or possess identifiers typical of classifiers (e.g., 'status', 'type'). To reduce false positives, it excludes obvious free-text fields (names, comments) and verifies that no table with a similar name currently exists. This suggests the need to extract these attributes into a new dedicated reference table to enforce domain integrity.

This query determines the maximum field size of text columns whose names suggest they store some type of code.