PostgreSQL Query Catalog

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.

Find base table columns that name refers to the possibility that these are used to register dates. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column data type is "date", then the suffix of the column name should be "kp" (Estonian) or "date" (English).

Find base table columns that name refers to the possibility that these are used to register timestamps. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column type is "timestamp", then the suffix of the column name should be "aeg" (Estonian) or "time" (English).

Find table columns with timestamp/date types that data type and dynamically found default value have a different type.

Find table columns with time types, which data type and dynamically found default value have a different type.

This query checks whether columns used to record registration and last update times have inconsistent data types across the database. Using varying data types for these temporal columns (for example, mixing timestamp and timestamptz) can lead to unexpected timezone behaviors and complicate time-based calculations.

Find as to whether there are multiple different types used in case of columns that are meant for registering dates.

Find whether the database uses both CHAR(1) and VARCHAR(1) columns to register a single symbol.

Find as to whether both the numeric type and integer types are used in case of columns that are meant for register data about prices.

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.

Find as to whether in the database there are multiple ways to calculate tsvector values, i.e., by using a generated column and by not using a generated column.

This query checks whether timestamp columns used to record registration times are named consistently across different tables. Maintaining a uniform naming convention (such as always using created_at or reg_time) significantly improves schema readability and maintainability.

This query checks whether timestamp columns used to record last update times are named consistently across different tables. Using a uniform naming convention (such as consistently using updated_at or last_modified) significantly improves database schema readability and maintainability.

Find as to whether different precisions/scales are used in case of registering data about sums of money in different columns.

Find as to whether different data types (with and without timezone) and precisions are used in case of registering times in different columns.

Find as to whether different data types (with and without timezone) and precisions are used in case of registering timestamps in different columns.

Find columns of base tables that have the default value CURRENT_USER or SESSION_USER but the data type is CHAR or TEXT.

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 potential schema discrepancies related to column sizing. It flags text columns in base tables where the static default value is unusually short—specifically, less than half the column's maximum allowed capacity.

This query identifies ineffectual CHECK constraints on base and foreign table columns that incorrectly attempt to prohibit empty or whitespace-only strings using the predicate trim(column_name) IS NOT NULL. Due to PostgreSQL's strict distinction between an empty string ('') and NULL, this check is a tautology; trim('') evaluates to '', and the condition '' IS NOT NULL is always true. The query finds these logically flawed constraints, which fail to provide any data validation and permit the insertion of the exact values they were intended to prevent.