PostgreSQL Query Catalog

Find base table columns that name refers to the possibility that these are used to register passwords. Return a value from each such column. Make sure that the password is not registered as open text.

This query identifies non-foreign key columns with a textual data type that lack essential validation. It specifically targets columns that are missing both of the following fundamental checks:

• A constraint to prohibit the insertion of empty or whitespace-only strings.
  
• A constraint to enforce a character set or format policy (e.g., via a regular expression).
  

This query identifies foreign key columns where the identifier deviates slightly from the referenced candidate key, specifically exhibiting a textual difference (Levenshtein distance) of two to four characters. This range typically captures minor prefixes (e.g., fk_) or suffixes that prevent the use of the concise SQL USING syntax in join operations. The query explicitly excludes self-referencing constraints (recursive relationships), where distinct column names are structurally mandatory. Aligning these names allows for cleaner, more readable query formulation.

This query identifies foreign key columns where the identifier exhibits a minimal textual deviation (exactly one character) from the referenced candidate key. This specific proximity often indicates a typographical error or a singular/plural inconsistency (e.g., user_id vs users_id). The query explicitly excludes self-referencing constraints, where name divergence is structurally mandatory. Harmonizing these names enables the use of the simplified SQL USING syntax for joins, replacing verbose ON clauses and improving query readability.

Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four).

Find cases where you store images and other media as files outside the database and store in the database only paths to the files.

Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.

Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row.

Find names that contain extremely short terms, due to an excessive word contraction, abbreviation, or acronym usage.

Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints.

Make sure that you do not drop INFORMATION_SCHEMA schema. In this case most of the design checking queries will not work. This schema automatically exists in all databases.

"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) Names should be expressive. Find the median and average (arithmetic mean) number of subcomponents in the names of user-defined database objects. The values could be used to compare different databases.

"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) Names should be expressive. Find the median length and average (arithmetic mean) length of the names (identifiers) of user-defined database objects. The values could be used to compare different databases.

"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) Names should be expressive. Find the median length and average (arithmetic mean) length of the names (identifiers) of user-defined database objects by the object type. The values could be used to compare different databases.

Find the names of simple primary key columns that name does not follow the pattern _id or _code but it is quite similar.

Find the total number of columns in the different types of tables as well as average number of columns in the tables as well as minimal and maximal number of columns.

This query audits the schema for semantic ambiguity by identifying pairs of routine names (functions, procedures) that exhibit high textual similarity but are not identical. It filters for name pairs with a Levenshtein edit distance of exactly one (less than two, but excluding equality). This specific filter targets typographical errors (e.g., calc_tax vs. calc_tux) or inconsistent singular/plural naming (e.g., get_user vs. get_users), while correctly ignoring valid method overloading where names are identical.

This query performs an intra-table analysis to detect potential schema ambiguities. It identifies pairs of columns within the same table that exhibit both high textual similarity and structural equivalence. Specifically, it flags pairs where the names have a Levenshtein edit distance of exactly one and the columns share the same data type or domain. This combination suggests a high probability of typographical errors (e.g., status vs statuss), inconsistent naming (singular vs. plural), or improper denormalization (e.g., item1 vs item2), all of which undermine schema clarity.

This query identifies potential redundancy or ambiguity in the schema by detecting pairs of user-defined domain names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters. Crucially, the query implements a filter to exclude pairs where the divergence is attributable solely to numerical digits. This heuristic prevents false positives for valid domain variations based on size or version (e.g., d_name_20 vs. d_name_50), focusing the analysis strictly on likely typographical errors or semantic duplicates.

This query identifies potential redundancy or ambiguity in the schema by detecting pairs of table names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters (i.e., a distance of 0 or 1). This check applies across different types of tables (base tables, foreign tables, derived tables), helping to uncover typographical errors (e.g., users vs user), inconsistent pluralization, or confusingly named entities that violate the principle of distinct and descriptive identifiers.