PostgreSQL Query Catalog

Find foreign key constraints where the candidate key columns (belong to a PRIMARY KEY/UNIQUE constraint) and foreign key columns do not have the same data type and field size. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values.

Find parameters of routines that have the same name but a different type. Parameters that have the same name should have, in general, the same data type as well, assuming that the routines, which have the parameters, have different names, i.e., there is no overloading in play.

In case of a chain of relationships between tables (where the primary key and the foreign key have the same columns) the use of ON UPDATE compensating action should be consistent. For instance, in the next example there is inconsistency, because if one changes the person_code in table Person, then the modification does not succeed because it does not cascade to the table Product. It is unclear as to whether it should be possible to change the person_code or not. Person (person_code, surname) Primary key (person_code) Worker(person_code)
Primary key (person_code)
Foreign key (person_code) References Person (person_code) ON UPDATE CASCADE

Product(product_code, registrator)
Primary key (product_code)
Foreign key (registrator) References Worker (person_code) ON UPDATE NO ACTION

This query audits the schema for inconsistent data validation logic. It identifies non-foreign key base table columns that share the same identifier (name) across different tables but enforce a divergent set of single-column CHECK constraints. According to the "Clean Code" principle of consistency, a recurring attribute name (e.g., status_code) implies a shared domain concept and should be subject to identical validity rules globally. Discrepancies indicate that business rules are being applied unevenly, potentially compromising data integrity.

This query identifies inconsistencies in data validation for columns that share the same name across different tables. It flags cases where a conceptual data element (e.g., 'email', 'postal_code') is subject to a CHECK constraint in some tables but lacks one in others. This violates the principle of uniform data integrity, creates semantic ambiguity, and can allow invalid data to enter the system through the unconstrained columns.

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 audits regular expressions within the database to detect syntactical inconsistencies in identifying numeric digits. It checks for the concurrent use of disparate character class notations: range-based ([0-9]), Perl-style shorthand (\d), and POSIX character classes ([[:digit:]]). While these are often functionally equivalent for standard ASCII digits, mixing multiple syntaxes within a single codebase indicates a lack of standardization, which reduces code readability and increases cognitive load during maintenance.

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.

"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 naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term. For instance, it is a bad practice to use word "state" in table names but word "status" in function names.

This query audits the naming conventions of BOOLEAN columns to detect inconsistency. It checks whether the database contains a mix of styles: some boolean columns adhering to a semantic prefix convention (e.g., is_active, has_permission) and others using unprefixed nouns or adjectives (e.g., active, permission). The presence of both styles indicates a lack of a consistent standard, which can reduce schema clarity and predictability for developers.

Find columns of tables that start with the word comment or komment but end differently (excluding numbers). Return result only if there is more than one naming variant of such columns in the database. For instance, a column has the name "comment" but another "comments".

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.

This query validates column naming consistency, specifically targeting generic attributes such as comments or descriptions. It flags tables that exhibit a mixed convention: containing both prefixed (e.g., table_comment) and unprefixed (e.g., description) generic columns within the same database context. This excludes primary and foreign keys. The goal is to enforce a uniform style—either consistently prefixing generic columns with the table name or consistently omitting the prefix—rather than allowing a hybrid approach.

This query audits the database for inconsistent column naming conventions, specifically focusing on non-generic, domain-specific attributes (excluding primary keys, foreign keys, and generic fields like 'comment'). It detects a hybrid naming strategy: some columns are prefixed with the table name (e.g., employee_salary) and others are not (e.g., hiring_date). This inconsistency suggests a lack of a standardized data dictionary, making query writing unpredictable.