| 881 |
Column names that make joining tables more difficult |
This query identifies foreign key columns where the identifier differs from the referenced candidate key identifier. It explicitly excludes self-referencing constraints (recursive relationships), where name divergence is structurally mandatory. The primary objective is to identify opportunities to harmonize column names across the schema. Synchronizing the foreign key name with the referenced column name facilitates the use of the ANSI SQL USING clause in join operations (e.g., JOIN t1 USING (client_id)), which is significantly more concise than the explicit ON predicate required when names differ. |
Problem detection |
system catalog base tables only |
2025-12-14 11:31 |
MIT License |
View |
| 882 |
Column names that make joining tables more difficult (quite similar names) |
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. |
Problem detection |
system catalog base tables only |
2025-12-14 11:29 |
MIT License |
View |
| 883 |
Column names that make joining tables more difficult (table names) |
This query identifies foreign key relationships where the identifier of the referenced column diverges from the foreign key column solely due to the inclusion of the table name. It specifically targets cases where the referenced column name is formed by concatenating the target table name with the target column name (e.g., referenced_col = table_name || '_' || fk_col). This naming redundancy prevents the use of the simplified SQL USING syntax for join operations, necessitating verbose ON clauses. Harmonizing these identifiers by standardizing the naming convention improves schema readability and query conciseness. |
Problem detection |
system catalog base tables only |
2025-12-14 11:53 |
MIT License |
View |
| 884 |
Column names that make joining tables more difficult (very similar names) |
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. |
Problem detection |
system catalog base tables only |
2025-12-14 11:30 |
MIT License |
View |
| 885 |
Columns of derived tables that name has been given by the system |
Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 886 |
Columns of derived tables that name has been given by the system (2) |
Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 887 |
Columns with array or user-defined type |
This query identifies columns defined with complex data types, specifically Arrays or User-Defined Types (UDTs). While PostgreSQL supports these advanced features, their indiscriminate use often signals an over-engineered schema. The query serves as a prompt to audit these columns and verify that the complex type is strictly necessary for performance or domain logic, and that a standard relational structure (e.g., scalar types or a child table) would not be a more appropriate and flexible design choice. |
General |
system catalog base tables only |
2025-12-12 17:23 |
MIT License |
View |
| 888 |
Comments of columns |
Find all comments of columns of tables. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 889 |
Comments of derived tables |
Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 890 |
Comments of non-derived tables |
Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 891 |
Comments of schemas, sequences, types, domains, domain constraints, and event triggers |
Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 892 |
Composite foreign keys |
Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 893 |
Composite foreign keys with a mix of mandatory and optional columns |
Find composite foreign keys with a mix of mandatory and optional columns. In case of a composite foreign keys all the columns should either optional or mandatory in order to avoid problems with NULLs. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 894 |
Composite foreign keys with an incorrect order of columns (ver 1) |
Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key consist of columns with the same name but the order of columns in the keys is different. For instance, the query returns information about a foreign key (personal_code, country_code) that refers to the candidate key (country_code, personal_code). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 895 |
Composite foreign keys with an incorrect order of columns (ver 2) |
Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key are not the same in terms of data types of the columns. For instance, the query returns information about a foreign key that columns have the types (SMALLINT, INTEGER) that refers to the candidate key that columns have the types (INTEGER, SMALLINT). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 896 |
Constraints that are not redefined in a subtable |
Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation) |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 897 |
Constraints that are redefined in a subtable. |
Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) and have been redefined in its subtable. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 898 |
Constraint-supporting UNIQUE indexes with the same leading column |
Find indexes that support a uniqueness constraint and have the same leading column. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 899 |
Constraints with the same name within the same schema and constraint type |
Find names of foreign key constraints that are used within the same schema more than once. Find names of check constraints that are used within the same schema more than once. Find names of constraint triggers that are used within the same schema more than once. Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 900 |
Coupling of distinct entity lifecycles via shared state classifiers |
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. |
Problem detection |
system catalog base tables only |
2026-01-19 17:36 |
MIT License |
View |